Help Excel question

Bahumat

FH is my second home
Joined
Jun 22, 2004
Messages
16,788
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

I am a FH squatter
Joined
Dec 23, 2003
Messages
16,209
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

FH is my second home
Joined
Jun 22, 2004
Messages
16,788
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

I am a FH squatter
FH Subscriber
Joined
Dec 23, 2003
Messages
18,426
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:

Code:
<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

Code:
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

Happy Shopper Ray Mears
FH Subscriber
Joined
Dec 27, 2003
Messages
44,616
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

I am a FH squatter
FH Subscriber
Joined
Dec 23, 2003
Messages
18,426
This relies on a text file called printers.txt in the same folder with each field separated by a space:
Code:
@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:
Code:
P1 1234567890 1
P2 2433624325 2
P3 7864534256 5

...and this is the output...
Code:
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

Happy Shopper Ray Mears
FH Subscriber
Joined
Dec 27, 2003
Messages
44,616
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.
 

Attachments

  • Printers.zip
    7.1 KB · Views: 5

Bahumat

FH is my second home
Joined
Jun 22, 2004
Messages
16,788
Thanks for the help guys. Gonna have a play around tomorrow :)
 

Jupitus

Old and short, no wonder I'm grumpy!
Staff member
Moderator
FH Subscriber
Joined
Dec 14, 2003
Messages
3,285
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

FH is my second home
Joined
Jun 22, 2004
Messages
16,788
Thanks @Raven this is perfect! Also good idea @Jupitus - I managed to find that part under Data Validation.

Cheers all!
 

Users who are viewing this thread

Top Bottom