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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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,459
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,620
Messages
5,832,734
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top