have tab name in formula pull from field

Microsoft Excel 2003 (full product)
February 4, 2010 at 12:10:01
Specs: Windows XP
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)


See More: have tab name in formula pull from field

Report •


#1
February 4, 2010 at 15:20:39
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.

Report •

#2
February 4, 2010 at 15:24:18
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)


Report •

#3
February 4, 2010 at 15:29:44
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 directly in 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.


Report •

Related Solutions

#4
February 4, 2010 at 16:12:32
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-1

I am not sure if B2 in the existing lookup is already a date, as the OP does mention lookup on date.

Regards


Report •

#5
February 4, 2010 at 19:48:00
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.


Report •

#6
February 4, 2010 at 21:13:48
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.


Report •

#7
February 4, 2010 at 21:34:59
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.


Report •

#8
February 5, 2010 at 03:35:42
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


Report •

#9
February 5, 2010 at 05:52:23
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


Report •

#10
February 5, 2010 at 09:53:07
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.

Report •

#11
February 5, 2010 at 10:03:44
Hi,

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

Regards


Report •

#12
February 5, 2010 at 11:10:17
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 a lookup_array in A1:B3 of both sheets.
I saved and closed the file.

In a new workbook, I created a lookup_array in 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: A15

which 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_array it did not update

=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_array and 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_array were 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: A15 again. I browsed to FebTest.xls, clicked on the Feb sheet and the new value from the [FebTest]Feb!lookup_array appeared 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_array inside the workbook and accessing it that way.


Report •

#13
February 5, 2010 at 13:58:03
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


Report •

#14
February 5, 2010 at 15:01:56
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.


Report •

#15
February 5, 2010 at 15:58:39
Hi DerbyDad03,

I realize that the closed workbook is not being 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


Report •

#16
February 5, 2010 at 16:13:55
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.


Report •


Ask Question