Picking data from a table 1

[quark]

Hi all,

The case is that I have two drop down lists in a worksheet and the relevant data is another worksheet of the same workbook. I have the drop down list in the cells, say, D8 and F8. The data in D8 corresponds to pipe size and that of F8 is sch. no. I arranged the database with pipe size in a column and sch.nos in a row. How is it that I can pick up the ID of pipe in a different cell of first worksheet, corresponding to a particular column and row intersection?

I checked some past threades and JWalkenbach's bible but couldn't get anything (ofcourse, I am a dummy in excel)

Secondly, is it possible to create a custom input to a dropdown list?

Thanks,

 

[COEngineeer]

do a search help on excel for VLOOKUP or LOOKUP.

For dropdown go to data, validation, change it to list on setting and then uncheck the error allert.

 

[quark]

Thanks. I spent an entire day on lookup and index functions but couldn't get any reference to data pickup at column row intersection.

 

[MortenA]

Its not that simple. You must use a nested structure. I usually do it with a Hlookup within a Vlookup - but you can also do it like this:
VLOOKUP(E2,pipetable!A2:S21,2+MATCH(E3,pipetable!C1:S1,0),FALSE)

where
In this example the data is store in the folloing way:
Nominal diameter is in the first collum, OD in the next and then follows wall thickness for each scehdule. It could off course also be ID instead og wall thickness.

E2 is the cell with the nominal diameter and E3 the schedule
range a2:s21 is entire table without the top line where schedule names are printed above each collum
range c1:s1 is the table with the two first collums where the nominal diameter and OD is listed

Best regards

Morten

 

[quark]

Excellent!!! Thanks Morten.

Is there a possibility to feed user inputs directly into the drop down list. I want to have a flexibility either to choose from the table or the user giving his own inputs.

Thanks once again.

 

[COEngineeer]

do what I said on second sentence. You need to uncheck the error message.

 

[MortenA]

There appears to be a typo in my text:

The last line should read:

range c1:s1 is the table withOUT the two first collums where the nominal diameter and OD is listed

and note that E2 and E3 is on a different sheet that the table - hence the reference to pipetable!

Also as stated - but a little hidden - the first line contains the name of each schedule (10,20,30, STD etc). They must be organised in an asending order as the diametres in the first collum also should be.

Best regards

Morten

 

[quark]

Morten,

Don't worry about the range. Once you told me the syntax, I fixed up the range. I have the pipesizes from J4 to J34 and schedules from K4 to W4 and the table is from J4 to W34. My formula was =VLOOKUP(D8,'Pipe Sizes'!J4:W34,1+MATCH('Pressure Drop'!F8,'Pipe Sizes'!K4:W4,0),FALSE)

D8 is the dropdown list for pipe size and F8 for schedule nos. It is working as precisely as a pick and place robot arm.

COEngineer,

That solves the issue of putting values in the cell, but somehow I have to include them in the calculation. I may try it using option buttons.

Thanks to both of you.

 

[joerd]

Quark,

You can give the user the opportunity to provide his own diameter by using a simple IF statement. You could normally provide the nominal size in one cell and the schedule in another cell. Make one of the options in the schedule cell "custom". If you select that, an IF statement then takes the entry in the nominal size cell as the actual real internal diameter, otherwise it calls the LOOKUP function.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[quark]

joerd,

Thanks. I tried to put that option in the pipe size cell and was getting circular reference error. Putting into "sch" cell would have solved it easily. However, I created another list with two options "user" and "standard" and included the if statement there and now working perfectly.