Microsoft Excel 2003 (full product)

I have 12 tabs, 1 for each month.

I have a vlookup that uses today's date and pulls some info.

However, when the month changes, I have to change the tab name. I'd like to be able to have the formula pull the month from the date, and then go to that month's tab. Formula below: [the FEB is the tab, and i don't know how to make that pull from today's date, so when the month changes, it pulls from the next tab]

=VLOOKUP($B$2,'H:[demo.xlsm]FEB'!$A$1:$N$8,4,FALSE)

Create a cell with =date() to retrieve today's date

In another cell put =month(referring to the above cell) to get today's month.

Create an array of cells with numeric month in one column and the text value corresponding to your tabs in the next column.

Now make another vlookup to check for the month value in the array and refer to the new vlookup cell in your original vlookup.

While I can't test the exact formula you need since I can't duplicate your path and filename, I can tell you the concept. However, you should know up front that this method will not work if H:demo.xlsm is closed. If you need to access H:demo.xlsm while is closed, you're going to need some custom VBA functions. Let us know...

You need to use the INDIRECT function to build the file name based on today's date.

Specifically, you need to extract the current month and insert it into your path.

In addition, since you appear to have names such as Jan, Feb, Mar, etc. you need to format that extraction to be a 3 digit string.

Let's start with that. This will produce a 3 letter month based on the current date:

=TEXT(DATE(2010,MONTH(TODAY()),1),"mmm")

The Year (2010) and Day (1) don't matter since we've formatted the string as mmm which will only display the first 3 letters of the current Month. They need to be there, but they don't ever have to change.

Next, we need to insert that string into the formula. However, since Excel won't be able to read the standard format, e.g.

'H:[demo.xlsm]FEB'!$A$1:$N$8

with "FEB" coming from the TEXT function, we need to turn the whole path into a text string and put it inside the INDIRECT function.

With some properly placed quotes, including the single quotes required, and a few Concatenation operators, we get this:

"'H:[demo.xlsm]"&TEXT(DATE(2010,MONTH(TODAY()),1),"mmm")&"'!$A$1:$N$8"

Now, putting that inside the INDIRECT function, we get this:

=VLOOKUP($B$2,INDIRECT("'H:[demo.xlsm]"&TEXT(DATE(2010,MONTH(TODAY()),1),"mmm")&"'!$A$1:$N$8"),2,0)

re: Now make another vlookup to check for the month value in the array and refer to the new vlookup cell in your original vlookup.That will indeed allow the OP to find a text string that corresponds to the current month, but that string cannot be used

directlyin the original VLOOKUP to refer to the sheet with that name.That string (or the cell that contains that string) would need to be used with the INDIRECT function as I noted in Response #2.

Hi DerbyDad03, As this formula only works when the other file (Demo.xlsm) is open, there is no value in including the drive or path specification inside INDIRECT()

I put the Date in B1 and used this formula:

=VLOOKUP($B$2,INDIRECT("'[Demo.xls]" & TEXT(DATE(YEAR(B1),MONTH(B1),1),"mmm") & "'!$A$1:$N$8"),4,FALSE)Then use a change event on selecting a date in B1 to trigger the file load if not already loaded.

Cell B1 uses a 'validation' list of dates

The list is:F 1 =TODAY() 2 =F1-1 3 =F2-1I am not sure if B2 in the existing lookup is already a date, as the OP does mention lookup on date.

Regards

No problem with leaving out the path name. As you said, there's no value. How about we trade a "no value"?

What is the value in using YEAR(B1) - and having Excel evaluate it - if all we need is a 3 letter month string?

Since we've both hard coded "1" as the day, there no danger of a problem with a leap year, so any year that Excel will accept will work.

Even? ;-)

BTW...The reason I put TODAY() in the formula was because the OP said:

"Formula below: [the FEB is the tab, and i don't know how to make that pull from today's date, so when the month changes, it pulls from the next tab]"I assumed the "that" and the "it" referred to the formula itself.

re: That will indeed allow the OP to find a text string that corresponds to the current month, but that string cannot be used directly in the original VLOOKUP to refer to the sheet with that name.I believe you are wrong. I did all the steps I mentioned in a test scenario and had no problem.

It's very possible that I don't understand what you are suggesting. Why not post the formulae you are using for clarification?

I'm always willing to learn new ways to get things done.

Thanks.

Hi DerbyDad03, I didn't hard code the year on the basis that the formula is going to look odd in a couple of years - In 2012 a formula that superficially looks wrong, with the wrong year.

As to not using TODAY(), I did that to provide a way to trigger the load of the file. Change the date and the change event can trigger code to test if the data file is loaded, and if not, load it.

Hi Grok Lobster,

I would be interested in seeing your formula as I had come to the same conclusion as DerbyDad03, and had to use INDIRECT(). I had tried it without INDIRECT() around the filename/tab/table range but it didn't work.

I would rather not use INDIRECT() as it is volatile.

Regards

Hi, Using a date in B1, the formula can be simplified to:

=VLOOKUP($B$2,INDIRECT("'[Demo.xls]" & TEXT(B1,"mmm") & "'!$A$1:$N$8"),4,FALSE)Regards

My formula looked almost identical to your except where you have FEB, I had a cell reference to the cell with the new vlookup that returns FEB.

Hi, Were you able to make this work without using INDIRECT() ?

Regards

This is something I have not seen before. I tried it twice to convince myself and it happened both times. It's way up there on the wierdness scale. I created a workbook called FebTest.xls.

I renamed 2 sheets to be Jan and Feb.

I put alookup_arrayin A1:B3 of both sheets.

I saved and closed the file.In a new workbook, I created a

lookup_arrayin A10:B12 to pull the month from a list (per Grok) and put this formula in A14:=VLOOKUP(2, $A$10:$B$12, 2, 0) which evaluated to Feb.

So far so good.

I then put this in A15:

=VLOOKUP(A1, A14!$A$1:$B$3, 2, 0)

When I hit enter, Excel popped up the window entitled:

Update Values: A15which was populated with the files in My Documents.

I clicked on FebText.xls which opened a window showing the 3 sheets in FebTest.xls. I clicked on Feb and the formula updated itself to read:

=VLOOKUP(A1,[A14]Feb!$A$1:$B$3,2,0)

The VLOOKUP then returned the value from the VLOOKUP in the closed file. If I changed the value in A1, the VLOOKUP worked just fine, pulling the correct value from Column 2 or dislaying #N/A if the value from A1 wasn't found..

But wait...there's more.

If I changed the VLOOKUP in A14 to pull Jan from the

lookup_arrayitdid notupdate=VLOOKUP(A1,[A14]Feb!$A$1:$B$3,2,0)

That formula just kept pulling data from the Feb sheet of FebTest.xls, so this process will not work to update sheet name via the other VLOOKUP.

But wait...there's even more!

I wanted to see if the formula was really pulling values from the closed workbook. I opened FebTest.xls and change the values in Column 2 of the

lookup_arrayand saved/closed the file.When I changed the entry in A1...ready?...I still got the

old values! Changing A1 continually pulled in the original values that were in the Feb worksheet, not my updated values.That tells me that the values from the [FebTest]Feb!

lookup_arraywere being stored in the workbook with the VLOOKUP and it wasn't pulling data from the closed workbook.It wasn't until I physically edited the VLOOKUP formula, at which time it popped up the

Update Values: A15again. I browsed to FebTest.xls, clicked on the Feb sheet and the new value from the [FebTest]Feb!lookup_arrayappeared in the cell.That once again tells me that the formula is not reading the closed workbook, but actually storing the values from the [FebTest]Feb!

lookup_arrayinside the workbook and accessing it that way.

Hi DerbyDad03, For a moment when I was working on the VLOOKUP formula I had a valid result when the referenced workbook was closed. It did seem that the referenced value was being retained.

I followed your steps and yes I could access values in the array in the closed workbook, but if I changed the month to Jan, I got the file dialog, and selecting the Feb.xls file again allowed me to access the array - this time the array in the Jan sheet.

Interesting - I didn't think that Excel would retain a whole array of cells in a closed workbook.

Regards

Regards

re: "I could access values in the array in the closed workbook"I think the subtlety here is that we are not really accessing the closed workbook. I base this on the fact that you can open the closed workbook and update the values in the array but it will have no impact on the VLOOKUP until you actually edit the VLOOKUP and go through the dialog box again.

I think what it is doing is storing that array in a "variable" (e.g. A14, in my case) within the workbook that contains the formula itself.

Try this:

=b!A1

You'll get the same dialog box and you'll have to browse to file and then a sheet.

However, if you then open the workbook and change the value in A1, the previous formula will not reflect the new value. It will not update the value until you edit the formula and chose the file again, thus reloading the variable with the array.

In any case, as far as I can tell, none of this helps the OP unless he's willing to settle for static data.

Hi DerbyDad03, I realize that the closed workbook is

notbeing accessed.It just appears to be accessing a closed workbook.

I was just stating that I was interested that Excel was retaining a chunk of data from the other workbook, even when the workbook was closed, AND was continuing to use it to return values as the lookup item changed.

The only option I can see is to use one of the VLOOKUP formulas with the INDIRECT worksheet Tab name and use a trigger to ensure that the workbook is open.

I would try using a cell with a date and a worksheet change event on the date cell.

Another option is to have the On Open event open the other workbook if necessary and use the before close event to close it.

Regards

Of course, we're still waiting to hear from the Grok Monster with the actual formula he used. This "new" feature we're discussing showed up when I tried to duplicate his

"My formula looked almost identical to your except where you have FEB, I had a cell reference to the cell with the new vlookup that returns FEB."If he made it work such that the reference to the sheet name returned by the "new" VLOOKUP automatically updated the original VLOOKUP with each change of the month - without using INDIRECT - I'd sure like to know how.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History