Lists

xcalibr

New Member
Joined
Sep 11, 2006
Messages
48
Example:
If I create a drop down list of products I produce, and let's say that each product has a different production lead time which I want to display next to the product, how can I create a function that would display the lead time for a specific product chosen from the drop down list?

Thanks.

Xcalibr
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
you will have to create another sheet lets say "Time"... type all those products on A column and their respective lead on corresponding cell of col B.. (i am assuming that you have 1000 products)

then come back to your original sheet and click the first cell where you want the lead time to appear and type this code:

(I am assuming here that your drop down list are on A col)

=IF(A1<>"",INDEX(Time!$A$1:$A$1000,MATCH(LEFT(A1,4),LEFT(Time!$B$1:$B$1000,4),0)),"")
 

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
sorry here is the corrected code:

=IF(A1<>"",INDEX(Time!$B$1:$B$1000,MATCH(LEFT(A1,4),LEFT(Time!$A$1:$A$1000,4),0)),"")
 

xcalibr

New Member
Joined
Sep 11, 2006
Messages
48
List

Thank you for the information.

I have followed your instructions to the letter but the corresponding value that I'm getting instead of the time value is "#N/A". I created a sheet called "Time". In column "A" I have the product names. In column "B" I have times associated with the build lead times. The original sheet has the drop down list of products located in cell "A1" along with your formula in cell "B2".

And again, the value returned is "#N/A"

Maybe I missed something.

Regards,

Xcalibr
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What type of dropdown list is it?

Can you post some sample data?
 

xcalibr

New Member
Joined
Sep 11, 2006
Messages
48
The list that I'm creating is done as follows:
Step 1: Menu> Data
Step 2: Validation
Step 3: Allow:
Step 4: "List" is then selected reflecting my data set.
This drop down list is located in column "A1" in the original sheet.

Then, I just created some test data within the "TIME SHEET" by adding letters "A-Z" in column "A" and numbers "1-26" in column "B".

I went back to the original sheet to test the model at which point is returned the "#N/A" value.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Well you can probably just use a VLOOKUP.

=VLOOKUP(A1, 'TIME SHEET'!A1:B26, 2,0)
 

xcalibr

New Member
Joined
Sep 11, 2006
Messages
48
Yes, that's great, thank you. How does your previous formula differ from the VLOOKUP function?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
My previous formula?.:eek:
 

Forum statistics

Threads
1,141,680
Messages
5,707,789
Members
421,527
Latest member
Tamiwsw

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