Drop down box

[ineedadedt]

OK Another q.. I have a drop down box with the info NJ 01, NJ 02, - NJ 06. I want to set up another cell with the corresponding manager. So if someone selects NJ 01 the sheet would auto fill with John Smith in a different box.

Any suggestions on this?

Thanks again

 

[ineedadedt]

To be honest I am not 100% sure.

Thanks

 

[Chard9401]

Sheet 1

Create a table to store your data
A..............................B.........................C
1 I.D Number................Name...................Age
2 abc123......................Joe Bloggs............18
3 def456......................David Beckham......28
4
5

etc make the table as big as you want. Then highlight all data in column A except the heading and name the area. To do this click in the box left of the Sum box where it displays the Cell number and name the area I.DNumber. Then Highlight the whole area of data except the headings and name this area aswell. e.g data

Once you have done this go to the sheet where you want it all happen.

Click in cell B9 then click data from the top of the page and then validation. From the first box select list from the drop down list. Leave the second box as it is and then in the bottom box type =I.DNumber. Then ok out of all of that.

This will then make cell B9 a drop down list.

Then go to cell C46 and enter the following into the sum bar
=VLOOKUP(C9,data,2,FALSE) the C9 part looks for wht is in this cell, the data part finds the data from the selected area, the number 2 counts how many cells across from what is in cell C9 and FALSE means i don't know

hopefully this is clear enough. It is really difficult to explain things via a message board. It would be easier just to send you a spreadsheet with it all on.

 

[Chard9401]

*click from the top of the page DATA and then validation

 

[Brian from Maui]

Maybe this will help. You can also use the lookup table for the Data Validation in C1 by going to Data/Validation/Allow, select list/Source and enter =INDEX(A1:B6,0,1)

Book1
	A	B	C	D
1	NJ01	BILL	NJ04	JOE
2	NJ02	BOB
3	NJ03	TONY
4	NJ04	JOE
5	NJ05	JOHN
6	NJ06	TIGER
Sheet1

 

[Chard9401]

hey Brian from Maui try helping me out no one can sort out my problems! I've been waiting for a responce for ages and no one has given me a solution

 

[ineedadedt]

Thanks everyone for your help. Unfortunately, I think I have my worksheet set up differently then what you use so the transition is a little harder on my end to figure out. I do appreciate all the help though.

I now have a great resource to use!

Thanks so much,
Eric

 

[Brian from Maui]

hey Brian from Maui try helping me out no one can sort out my problems! I've been waiting for a responce for ages and no one has given me a solution

Try using Colo's utility to post a small sample of your table and formula. I've tried recreating you problem, but cannot come out with the #REF error. Usually when there's a #REF error message using Vlookup, it's because the column to lookup is outside the lookup table. But post to your original thread for this.

 

[Chard9401]

sorry, where can i post a screen dump of my error?

 

[Chard9401]

i am at work at the moment and i am unable to from this computer. Would i be able to email you my spreadsheet to an email address?

 

[Brian from Maui]

i am at work at the moment and i am unable to from this computer. Would i be able to email you my spreadsheet to an email address?

Check your PM