[Intern Help] Change worksheet reference within a formula

[djmelanson]

Hi, I have a multiple worksheets labeled as the months (January, February, March, April...)

On another sheet I have the date as numbers (6/4/2015)

How can I make it so that the second workbook with the date as numbers will reference from the right worksheet in the first workbook?

My current formula is: =VLOOKUP(A7,'[Line Stop - Hydro.xlsx]June'!$A:$AH,34,FALSE)

All I need changed within this formula is the month.

 

[RoryA]

Which cell has the date in it?

You can use INDIRECT for this, but the source workbook must be open.

If A7 is the date cell, you can use:

=VLOOKUP(A7,INDIRECT("'[Line Stop - Hydro.xlsx]"&TEXT(A7,"mmmm")&"'!$A:$AH"),34,FALSE)

 

[djmelanson]

D2 is the cell with the date in it. So it would be =VLOOKUP(A7,INDIRECT("'[Line Stop - Hydro.xlsx]"&TEXT(D2,"mmmmm")&"'!$A:$AH),34,FALSE) ?

I tried entering this formula and it came up with an error just saying "The formula you typed contains an error."

 

[RoryA]

There was a missing quotation mark after the $AH - I have amended my post.

 

[djmelanson]

Now it's coming up with a #REF! error in the cell. I checked all of the references within the formula and they all seem accurate.

 

[RoryA]

Apologies - another typo - it should be "mmmm" and not "mmmmm".

 

[djmelanson]

You are a saint! Thank you so much!!

 

[RoryA]

Glad to help.