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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

djmelanson

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There was a missing quotation mark after the $AH - I have amended my post.
 

djmelanson

New Member
Joined
Jun 3, 2015
Messages
17

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Apologies - another typo - it should be "mmmm" and not "mmmmm".
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

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
Top