Drop down lists - Data Validation

Piedda

New Member
Joined
Dec 14, 2005
Messages
2
Is it possible to populate multiple column cells from selecting only one item from a drop down list, all in the same row? e.g. Column A represents codes, column B is the description and column c is a price. By only selecting a code form the drop down list in column A, how can the next two columns B&C be populated automatically?? Drop down in A1 it then needs to populate B1 to J1 etc...

I would like to utilised this function in my invoices in order to save time not having to copy and paste this information from another worksheet
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
If you have a table with the codes, description and price you can use Vlookup based on the drop down value
 

Piedda

New Member
Joined
Dec 14, 2005
Messages
2
Thanks MichaelRo

I thought of utilising Vlookup but was not 100% sure. I'm now finding an error message #N/A because the first column has no value selected form the dorp down list.

I solved the probelm with an IF statement but once I select a value from the drop down list it now gives me another error #NAME.

SO its a catch 22 situation. Any suggestions??

Thanks Piedda
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Piedda said:
Thanks MichaelRo

I thought of utilising Vlookup but was not 100% sure. I'm now finding an error message #N/A because the first column has no value selected form the dorp down list.

I solved the probelm with an IF statement but once I select a value from the drop down list it now gives me another error #NAME.

SO its a catch 22 situation. Any suggestions??

Thanks Piedda

Post the IF formula.
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Edit:

What about this?

2nd Edit:
Book4
ABCDEFG
1B-20CodesDescriptionPriceCodesDescriptionPrice
2A-10Window$10.00B-20Tile$20.00
3B-20Tile$20.00
4C-30List$30.00
5D-40Frame$40.00
6
Sheet1



Data - Validation - List in A1, the range is B2:B5 in this case.

B1:D5 and E1:G1 plain text.

E2: =VLOOKUP(A1,B2:D5,1)
F2: =VLOOKUP(A1,B2:D5,2)
G2: =VLOOKUP(A1,B2:D5,3)

Note that the range, B2:D5, is the same in E2, F2 and G2, and that the column index number (the single last number in the VLookup formula) changes from 1-3 depending on in what order you find the column within the range B2:D5 (B is the first column, C the second…..).

HTH

RAM
 

Watch MrExcel Video

Forum statistics

Threads
1,119,123
Messages
5,576,228
Members
412,709
Latest member
AD04
Top