Drop Down box - value selection initiates auto population of subsequent fields

[kellerama]

Good luck understanding my confusing description below:

I am making an invoice template ("InvoiceWorksheet") to be used fo
multiple products from multiple manufacturers.

I would like to select a product from a dropdown box A1 and have al
subsequent fields automatically populate with information about m
chosen product based on info from the "DataWorksheet" (a secon
worksheet within this Excel file).

_InvoiceWorksheet_setup:_
Cell A1 - I select BlueWidget from a dropdown.
Cell A2 - automatically populates with BlueWidgetManufacturerName.
Cell A3 - automatically populates with BlueWidgetWholesale Price.
etc.

_DataWorksheet_setup:_
Column 1 - Product names (mapped to dropdown box A1 o
InvoiceWorksheet)
Column 2 - Manufacturer Name
Column 3 - Wholesale Price

_Excel_function_I_started_with_is:_
=IF(logical_test,value_if_true,value_if_false)

_One_of_my_dismal_attempts:_
=IF(A1=DataWorksheet!A1,DataWorksheet!A2,if(A1=DataWorksheet!B1,DataWorksheet!B2))

1) First of all, if the TRUE result is a value from the othe
worksheet, should it be listed in "" or [] or nothing?

2) The "FALSE" option: If the logical test proves FALSE, I want it t
do an IF formula to find out if a different value in the dropdown cel
is TRUE. Should that portion of the formula start a new ()? Is it t
come right after the comma as I did above?

3) I have over 300 products that will be on the dropdown list.

I hope I haven't confused the reader beyond belief

 

[Debra Dalgleish]

You can use a VLookup formula to return a value from a table. There are
instructions in Excel's Help, and here:

http://www.contextures.com/xlFunctions02.html

Good luck understanding my confusing description below:

I am making an invoice template ("InvoiceWorksheet") to be used for
multiple products from multiple manufacturers.

I would like to select a product from a dropdown box A1 and have all
subsequent fields automatically populate with information about my
chosen product based on info from the "DataWorksheet" (a second
worksheet within this Excel file).

_InvoiceWorksheet_setup:_
Cell A1 - I select BlueWidget from a dropdown.
Cell A2 - automatically populates with BlueWidgetManufacturerName.
Cell A3 - automatically populates with BlueWidgetWholesale Price.
etc.

_DataWorksheet_setup:_
Column 1 - Product names (mapped to dropdown box A1 on
InvoiceWorksheet)
Column 2 - Manufacturer Name
Column 3 - Wholesale Price

_Excel_function_I_started_with_is:_
=IF(logical_test,value_if_true,value_if_false)

_One_of_my_dismal_attempts:_
=IF(A1=DataWorksheet!A1,DataWorksheet!A2,if(A1=DataWorksheet!B1,DataWorksheet!B2))

1) First of all, if the TRUE result is a value from the other
worksheet, should it be listed in "" or [] or nothing?

2) The "FALSE" option: If the logical test proves FALSE, I want it to
do an IF formula to find out if a different value in the dropdown cell
is TRUE. Should that portion of the formula start a new ()? Is it to
come right after the comma as I did above?

3) I have over 300 products that will be on the dropdown list.

I hope I haven't confused the reader beyond belief!

 

[BenjieLop]

On of your InvoiceWorksheet, enter the ff formula in:

Cell A2: =vlookup(A1,DataWorksheet!$A2:$C302,2,0)

Cell A3: =vlookup(A1,DataWorksheet!$A2:$C302,3,0)

where A1 contains the name of your product taken from your drop dow
list.



<< I would like to select a product from a dropdown box A1 and have al
subsequent fields automatically populate with information about m
chosen product based on info from the "DataWorksheet" (a secon
worksheet within this Excel file).

_InvoiceWorksheet_setup:_
Cell A1 - I select BlueWidget from a dropdown.
Cell A2 - automatically populates with BlueWidgetManufacturerName.
Cell A3 - automatically populates with BlueWidgetWholesale Price.
etc.

_DataWorksheet_setup:_
Column 1 - Product names (mapped to dropdown box A1 o
InvoiceWorksheet)
Column 2 - Manufacturer Name
Column 3 - Wholesale Price >