×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Picking data from a table

Picking data from a table

Picking data from a table

(OP)
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,

RE: Picking data from a table

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.

RE: Picking data from a table

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

RE: Picking data from a table

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

RE: Picking data from a table

(OP)
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.

RE: Picking data from a table

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

RE: Picking data from a table

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

RE: Picking data from a table

(OP)
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.

RE: Picking data from a table

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.

RE: Picking data from a table

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources