Lists

[xcalibr]

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

 

[irresistible007]

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]

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]

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]

What type of dropdown list is it?

Can you post some sample data?

 

[xcalibr]

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 you can probably just use a VLOOKUP.

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

 

[xcalibr]

Yes, that's great, thank you. How does your previous formula differ from the VLOOKUP function?

 

[Norie]

My previous formula?.

 

[xcalibr]

yes