validation+vlookup

[niyrho]

Does anyone know of a way have excel show information from one column in a drop down box and when you select something it outs in the information from the next column?

 

[Claymationator]

You can easily set this up. What are the variables that you want to have returned, and what would your list consist of? You will need to provide some more information in order to get a specific result.

 

[niyrho]

I have one worksheet set as a database. In column A I have device types, for fire systems, smoke detectors, heat detectors and such. In column B I have the abbreveations for them.

I have another worksheet set up as an inspection report. Column A in that sheet is for the device types. I need drop down lists the show the whole name for the device, but I want it to enter the abbreveation when you select something.

So you click on the drop down and it refers to column A in the other sheet. Then you can choose, for example, Photo Electric Smoke Detector. Then it will enter the abbreveation from column B. Kinda makes it idiot proof. Huge plus with some of my guys.

 

[Claymationator]

OK, so I have set up a sheet that goes along what you are trying to do...as I understand it. On Sheet1 I have set up mine to look like this:

Book1
	A	B	C	D
1	DeviceTypes	Abbreviations
2	FireSystem	FS
3	SmokeDetectors	SD
4	HeatDetectors	HD
5	OtherTypes	OT
Sheet1

Then what I did was highlight the Device Types (A2:A5) and make a named range from it called "devicetype". Then on Sheet2 in A2 I make a data validation list and the range is set as "=devicetype". Then on Sheet2 in column B I put in the VLOOKUP to pull the abbreviation from Sheet1, the formula is as follows:

=VLOOKUP(A2,Sheet1!A2:B5,2,0)

Book1
	A	B	C	D
1	InspectionReport	Abbreviation
2	OtherTypes	OT
Sheet2

From your post it seems that is what you are looking for. If I have missed the mark let me know and I will make adjustments.

Hope that helps.

 

[niyrho]

Thats pretty close to what I need. Only problem is, on sheet 2, I can only have one column for that. So what I use the dropdown list in, say, A2, I can select smoke setector and in that same cell it put in SD instead of smoke detector.

I'm trying to combine idiot proof with regulation and company policy.

 

[Claymationator]

I am not aware of any way to have that happen and select an option from the validation dropdown and then return the abbreviation in the same cell...If it is possible it is beyond my knowledge.

Anyone out there that knows if that is possible?

What is the need for returning the abbreviation only? Are there other lookups or something within the sheet that need that, or can you just return the device type and still get the necessary results?

 

[niyrho]

There's a number of reasons I need it to work that way. Some of the devices names are far to long to write out on the device list for an inspection. It keeps things alot neater and easier to read. Most fire marshals don't want to read that much, they insist, sometimes demand that we use the abbreveation. And alot of times it helps aviod arguing with our customers. The customers have no idea what the abbreveations stand for, to them it just looks really really technical. And therefore think its all way over their heads and leave it alone. If they see something we had to fail on the report and they can see what the device they always give us alot of crap about it. They'll see that we had to fail something that they personaly don't care about and want us to pass it anyway. It doesn't bother them that by law we can't do that and would lose our license if we do.

Fire alarm techs deal with alot of ******** from people that just don't wanna understand.

 

[Claymationator]

Is there a way that you can post the sheet that you are dealing with so that we can see and possibly come up with a workaround for you?

 

[niyrho]

I'm not sure how I would do that. Its all on another computer that has no internet access.

 

[Claymationator]

If you can get a copy of it you can post it by following the directions on the link below:

http://www.mrexcel.com/forum/showthread.php?t=89356

If you can't get an actual copy you can make an example and paste it into the forum in the same manner.