[Intern Help] Change worksheet reference within a formula

djmelanson

New Member
Joined
Jun 3, 2015
Messages
17
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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)
 
Last edited:
Upvote 0
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."
 
Upvote 0
There was a missing quotation mark after the $AH - I have amended my post.
 
Upvote 0
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.
 
Upvote 0
Apologies - another typo - it should be "mmmm" and not "mmmmm".
 
Upvote 0
Glad to help. :)
 
Upvote 0

Forum statistics

Threads
1,216,481
Messages
6,130,921
Members
449,606
Latest member
jaybar0812

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top