PDA

View Full Version : Static Workbook but dynamic sheet - Indirect lookup



ashleyuk1984
12-03-2015, 10:07 AM
Hi,
I've been pulling my hair out for the last 2 hours trying to get this to work.

I have two workbooks.
1. The reference log with all of the details
2. A smaller log with specific details

Basically I'm putting a specific reference in log 2 along with the month, and then I'm pulling some details from log 1.
There are multiple sheets on log 1 (these are months)... So that's why I need to reference the sheets dynamically with the input that I'm putting in.

This formula works:


=INDEX('[IN USE Import copy of import entry log V13.xlsm]11. November 15'!$A$1:$AD$5000,MATCH(LEFT(A938,9),'[IN USE Import copy of import entry log V13.xlsm]11. November 15'!$A:$A,0),30)

But as you can see, I'm limited to only the November tab.
What I would like is for a specific cell to determine the sheet for me.

I need something like this:

=INDEX('[IN USE Import copy of import entry log V13.xlsm]&INDIRECT("C2")&'!$A$1:$AD$5000,MATCH(LEFT(A938,9),'[IN USE Import copy of import entry log V13.xlsm]11. November 15'!$A:$A,0),30)

Where cell C2 is the cell where I input the month.
But I can't get the formula correct.

Does anyone know how to write the formula so that Excel accepts it?

I don't need to change the source workbook at all, it's just the sheet.

ashleyuk1984
12-05-2015, 06:34 PM
Sorry to bump, does anyone know to do this?
I only need the sheet to be dynamically changed depending on the cell value. But the workbook is perfectly fine how it is.

Thanks

mancubus
02-23-2017, 01:58 AM
try adopting below formula which i currently use.


=COUNTA(INDIRECT("'"&C5&"'!$"&E5&"$4:$"&E5&"$"&F5))

C5: sheet name
E5: column letter
F5: number of the last row of the range

mancubus
02-23-2017, 12:47 PM
sheet name in C2

=INDEX(INDIRECT("'[IN USE Import copy of import entry log V13.xlsm]"&C2&"'!$A$1:$AD$5000"),MATCH(LEFT(A9,9),INDIRECT("'[IN USE Import copy of import entry log V13.xlsm]"&C2&"'!$A:$A"),0),30)

sheet name in C2 and file name in D2

=INDEX(INDIRECT("'["&D2&"]"&C2&"'!$A$1:$AD$5000"),MATCH(LEFT(A9,9),INDIRECT("'["&D2&"]"&C2&"'!$A:$A"),0),30)