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.
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.