Creating a dynamic VLOOKUP using INDIRECT based on the contents of a cell + additional txt

[Typirious]

Hi guys and girls,

I'm in the process of updating a bunch of excel spread sheets for my company and have hit a brick wall building a dynamic vlookup that uses indirect to take the contents of a cell and add to it additional text to dynamically navigate to other sheets within the same workbook.

So Currently my formula in cell H97 looks like this:

Please Login or Register  to view this content.

Everything works well and dandy, but what I am after is changing the 'COSTCO RAW DATA' to an INDIRECT using cell A1 which contains "COSTCO" and adding the rest to the formula, so it will look something like this as a wild guess:

Please Login or Register  to view this content.

^ Obviously this is wrong and throws a #REF once i remove the IFERROR Clause - Please let me know where and what i'm doing wrong as i'll be playing with dynamic lookups for a couple of weeks. Thanks in advance!

[benishiryo]

hi there. the only problem with your INDIRECT formula is that you shouldn't need to end the A1 formula with a single quote (that is already after the RAW DATA) and there is no space for RAW DATA. so:
=IFERROR(VLOOKUP($A96,INDIRECT("'"&$A$1&" RAW DATA"&"'!$A:$GC"),COLUMN(),0),0)

wont do any harm, but for INDIRECT, there's no need to fix the column.
=IFERROR(VLOOKUP($A96,INDIRECT("'"&$A$1&" RAW DATA"&"'!A:GC"),COLUMN(),0),0)

[Typirious]

Worked a treat! Thank you, and thanks for the info about not needing to fix the column when using an INDIRECT!

Regards,

Typirious