Help Excel question

[Bahumat]

at work we have printers with specific names. Let's say they are called P1, P2, P3 etc.

When we call out our printer support they reference the printers via a serial number. Let's for example sake call these serial1, serial2, Serial3 etc.

I have zero coding knowledge but I wanted to have a little program where you enter the printer name, it checks a data source and then shows the printer name, serial number and the floor it's on.

Someone told me you can do this with excel using vb? Does anyone have any clue how I do this please?

To summarise; I would like sheet one of the excel spreadsheet to be like a window with one field. When you enter the printer number it shows the other information below.

 

[Moriath]

Should be pretty easy. You need to make a second sheet with all that information in a table. Then get the first sheet to do a lookup when the printer name is entered and return the data.

 

[Bahumat]

Thanks Moriath. Can you elaborate a bit on the lookup and return part please.

Like is this under a menu in excel or do I have to go into VB mode? Are there a series of steps I need to go through first? If so what do you think I should use?

normally when I look online for help on excel people say (for example) "oh yeah use form control" as if everyone knows what that is. Like I say, I'm pretty clueless when it comes to excel.

 

[caLLous]

You could just have a text file with a line per printer and a cheeky little batch/script to do this, no need to overcomplicate it.

The text file would be like:

<printer name> <serial number> <floor>

Then you'd have the batch file which would run from the command prompt and take the printer name as a parameter, like

printer.bat P1

and it would take that parameter and search the text file for it, returning the info it found on the line matching the printer's name.

 

[Raven]

Or you can keep it simple with Vlookups

Attempting to PM it to you as I cant seem to attach excel files here...

Edit....aaaand no, cant send them as PMs either...

 

[caLLous]

This relies on a text file called printers.txt in the same folder with each field separated by a space:

@echo off
for /f "tokens=1,2,3" %%a in ('findstr %1 printers.txt') do (
   echo printer: %%a  serial: %%b  floor: %%c
)

Save that into a file called printer.bat (or <whatever>.bat) and open up command prompt, goto the folder both files are in and type "<whatever>.bat <printer name>".

This is the content of the test file I used:

P1 1234567890 1
P2 2433624325 2
P3 7864534256 5

...and this is the output...

D:\>printer.bat P1
printer: P1  serial: 1234567890  floor: 1

D:\>printer.bat P2
printer: P2  serial: 2433624325  floor: 2

D:\>printer.bat P3
printer: P3  serial: 7864534256  floor: 5

 

[Raven]

Try this. The field you change is yellow, the sheet does the rest. I have made it so you have to pick from a list using data validation but you can change it to whatever you like, so long as its on the list.

 

[Bahumat]

Thanks for the help guys. Gonna have a play around tomorrow

 

[Jupitus]

If you don't like Raven's data validation you might want to change the formulae used so that an invalid printer name being entered returns a meaningful error... post here or pm me if that's the case... it's very easy to do.

 

[Bahumat]

Thanks @Raven this is perfect! Also good idea @Jupitus - I managed to find that part under Data Validation.

Cheers all!