Use value in one cell to paste a corresponding row

[JBR87]

Hi guys,

This is my first post on this forum but i've read alot trying to find a solution to this problem. I am creating an takeoff sheet for a steel company and need a bit of help.

In the "take off" worksheet I have a column "description" In description a user will type in a steel section (example: W250x49) information about W250x49 is stored in the worksheet "W". What I would like to have excel do is search through my worksheets in order to fin the section that was entered into the "description" row and pull corresponding info automatically. This has to work with other sections as well for example if a HSS305x305x16 is typed in "description", excel will pull data from the HSS-G.40 worksheet.

I've attatched an example excel file to help explain.

Any help would be apreciated
thanks,
Jeff

[JBeaucaire]

Pull what corresponding info? Your sample sheet needs more complete examples. You've only listed on sample description an no sample results. Add a few more.

Also, your source sheets probably all need to ensure the results columns are the same on all sheets. They don't all appear to be the same. Some sheets have a "Use" column and others don't.

I have a 3D Vlookup formula that will do this, but the columns would need to align, at least for the columns you want to pull data from.

[JBR87]

Pull what corresponding info? Your sample sheet needs more complete examples. You've only listed on sample description an no sample results. Add a few more.

Also, your source sheets probably all need to ensure the results columns are the same on all sheets. They don't all appear to be the same. Some sheets have a "Use" column and others don't.

I have a 3D Vlookup formula that will do this, but the columns would need to align, at least for the columns you want to pull data from.

I have taken your advice and made some changes to my attatchemt, the new one is included in this post. All I really need to return is the corresponding section mass so I got rid of all the other colums as they are useless for this tool, this allows me to have the same result column on each sheet.

I hope this helps defog my situation.

Jeff

[JBeaucaire]

First we create a list of sheet names. It's good that they're all one-word names. I created the list in column O.

Then in Insert > Name > Define I created a dynamic named range for column O called MySheets with this dynamic formula:

=OFFSET('Take off'!$O$1,,,COUNTA('Take off'!$O:$O),)

This will cause the named range MySheets to automatically expand/contract as you change the entries in column O.

Now in G10 we enter our first array formula:

=VLOOKUP(A10, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!B5:B300"), A10), 0)) & "'!B:Z"), 2, 0)

...and confirm that formula by pressing CTRL-SHIFT-ENTER to activate the array.

This formula will search the sheets listed in column O for the value in column A. If it is found, it will return the value from column C of that sheet. Adjust the 2 to 3 or 4 for values from columns D or E of those sheets.

Now that the first value is there, copy that cell downward. You'll need to correct the value in A13, it doesn't exist. I used HS152x76x9.5

[JBR87]

First we create a list of sheet names. It's good that they're all one-word names. I created the list in column O.

Then in Insert > Name > Define I created a dynamic named range for column O called MySheets with this dynamic formula:

=OFFSET('Take off'!$O$1,,,COUNTA('Take off'!$O:$O),)

This will cause the named range MySheets to automatically expand/contract as you change the entries in column O.

Now in G10 we enter our first array formula:

=VLOOKUP(A10, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!B5:B300"), A10), 0)) & "'!B:Z"), 2, 0)

...and confirm that formula by pressing CTRL-SHIFT-ENTER to activate the array.

This formula will search the sheets listed in column O for the value in column A. If it is found, it will return the value from column C of that sheet. Adjust the 2 to 3 or 4 for values from columns D or E of those sheets.

Now that the first value is there, copy that cell downward. You'll need to correct the value in A13, it doesn't exist. I used HS152x76x9.5

Thank you that works like a charm

Jeff

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[JBR87]

First we create a list of sheet names. It's good that they're all one-word names. I created the list in column O.

Then in Insert > Name > Define I created a dynamic named range for column O called MySheets with this dynamic formula:

=OFFSET('Take off'!$O$1,,,COUNTA('Take off'!$O:$O),)

This will cause the named range MySheets to automatically expand/contract as you change the entries in column O.

Now in G10 we enter our first array formula:

=VLOOKUP(A10, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!B5:B300"), A10), 0)) & "'!B:Z"), 2, 0)

...and confirm that formula by pressing CTRL-SHIFT-ENTER to activate the array.

This formula will search the sheets listed in column O for the value in column A. If it is found, it will return the value from column C of that sheet. Adjust the 2 to 3 or 4 for values from columns D or E of those sheets.

Now that the first value is there, copy that cell downward. You'll need to correct the value in A13, it doesn't exist. I used HS152x76x9.5

So here is a wierd issue. Everything works as it should but I've noticed something odd.

when entereing for example: w250x28 or w250*28 I get the right wieght, entering w250x49 gives me the wieght value as well but for some reason when I enter w250*49 I do not get a return

it seems as though the * is recognized as an "x" in some cases but not in all cases.

do you have any clue as to why this happens?

Thanks,
Jeff

[JBeaucaire]

Yes, in Excel the asterisk is a wildcard, you've effectively indicated "any characters from this point on". Don't use asterisks in text strings unless you like headaches.