Originally Posted by
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
Bookmarks