Changing the date within a Vlookup formula 2

[Mizzness]

All.

I have a vlookup formula that includes a date within the file it is referencing.
How can I copy this down and have the date change ?
Example:=VLOOKUP(A4,'U:\MBS\Whitesheets\2005\Nightly Board\March\[mar 21.xls]Sheet1'!$A:$L,12,FALSE)would have to change to mar 22 when copied down.

Thanx in advance.

 

[AnotherHiggins]

Have a look at the indirect function.

It will look something like this:
[COLOR=blue white]=VLOOKUP(A4,[red]indirect("[/red]'U:\MBS\Whitesheets\2005\Nightly Board\March\mar[red] " & row()+20 & "[/red].xls]Sheet1'!$A:$L,12,FALSE[red]")[/red])[/color]



[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red] R.I.P Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Mizzness,

You can't in a formula.

What you can do is create another column that contains the DATE that corresponds to the Workbook Name. Then use the INDIRECT function
[tt]
:=VLOOKUP(A4,INDIRECT("'U:\MBS\Whitesheets\2005\Nightly Board\March\["&Text(AA4,"mmm dd")&".xls]Sheet1'!$A:$L"),12,FALSE)
[/tt]
where AA4 contains the date for the workbook in qestion.

Copy the dates down, copy the expression down.

FYI:

Consider a better design in the future. Chopping similar data up into separate workbook, worksheets or the like leads to problems when it comes to data analysis and reporting. Hence, your post!

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[AnotherHiggins]

Duh. Mine won't do you much good when the month changes. You could incorporate my idea of using the row to increase incrementally with a beginning date.

Still, as Skip points out, your life would be much simpler if you had all of your data in a single table.

NOTE: With your current structure, you'll eventually need to take the year into account as well.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red] R.I.P Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

BTW,

Another concept to consider: How data is STORED and how data is REPORTED are often two different animals. Y

ou might want to report a day's worth, week's worth, month's worth, quarter's worth, year's worth (or some other aggregate) of data.

When the SOURCE data is contiguous and properly related (normalized) then this kind of reporting can be done under most circumstances, with the tools that have been bundled with Excel -- Sorts, Filters, Subtotals, Pivot Tables/Charts, Charts.

THEN, if you need to hack off a year to send to some Manager, Director or VP, it's a simple report function.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Skip,

After creating another column for the date, I am getting a #REF error. My formula is as follows:=VLOOKUP(F3,INDIRECT('[P&L Var Summary ["& Text(G3,"mm dd)]]P&L Var Summary ["& Text(G3,"mm'!F:I),2,FALSE)

This is based on the following formula instead of the original posted:=VLOOKUP(A3,'P&L Var Summary 03_01'!A,2,FALSE)

Thanx.

 

[Mizzness]

To summarise my last post, I am referencing a tab called P&L Var Summary 03_30_05.

My formula is as follows:
=VLOOKUP(A3,INDIRECT("'P&L Var Summary["& TEXT(G3,"mm_dd_yy")]'!,F:I),2,FALSE)


Thanx.

 

[byundt]

Mizzness,
The right and left brackets are used to surround workbook names, not sheet names. And you must preceded the underscore character by a backslash \, otherwise you get a space as wide as the character following the underscore in the TEXT function. Try it like this:

=VLOOKUP(A3,INDIRECT("'P&L Var Summary "& TEXT(G3,"mm\_dd\_yy") & "'!F:I"),2,FALSE)

Brad

 

[Mizzness]

Brad,

No luck with your formula.
This is what I have: =VLOOKUP(A4,INDIRECT("'P&L Var Summary "& TEXT(G4,"mm\_ dd\_ yy")"'!F:I"),2,FALSE)

G4 = 03/30/05 for the date value
Each tab is called as P&L Var Summary mm_dd_yy

Did I miss something ?

Thanx.

 

[Mizzness]

Sorry my formula is:

=VLOOKUP(A4,INDIRECT("'P&L Var Summary "& TEXT(G4,"mm\_dd\_yy")& "'!F:I"),2,FALSE)

 

[byundt]

Mizzness,
The formula is working in my test workbook, exactly as you posted it.

Do either column F or cell A4 contain trailing spaces? If so, you need to get rid of them before the VLOOKUP will work.

Does G4 contain a date/time serial number, or merely text that looks like a date? Try changing the format of cell G4 to mm-dd-yyyy. If the appearance doesn't change, then that column probably contains text.

To convert text that looks like dates into date/time serial numbers, here are two good alternatives:
1) Select all your "dates", then use the Data...Text to Columns menu item. In the second step of the wizard, choose the date format your data has in the dropdown on the top right.
2) Copy a blank cell, then select all your "dates" and use the Edit...Paste Special...Add menu item. Format the resulting numbers to the desired date format.

Brad

 

[Mizzness]

Brad,

Thanks.
Will try that and get back to you in the morning.

Anthony

 

[Mizzness]

Brad,

I'm getting a #N/A reply.
The formula is: =VLOOKUP(G4,INDIRECT("'P&L Var Summary "& TEXT(H4,"mm\_dd\_yy") & "'!F:I"),2,FALSE)

Column H is the date column. I checked the formatting & it's fine.
The tab I'm referencing is P&L Var Summary 03_02_05.

I agree that it should work but am perplexed.

Thanx.

 

[byundt]

Anthony,
Please replace the INDIRECT part of the formula with the worksheet name and reference to columns F and I:
=VLOOKUP(G4,'P&L Var Summary 03_02_05'!F:I,2,FALSE)

This formula ought to be returning the #N/A error value, just like the one using INDIRECT. If so, it's back to checking for extra spaces in either G4 or column F.
Brad

 

[Mizzness]

Brad,

That brings me back to step one.
I'm looking for a formula that I can copy down and have the date change per the tab I'm referencing.

 

[byundt]

Anthony,
The INDIRECT function is working in my test workbook. If it isn't working at your end, then something may be wrong in the data.

VLOOKUP returns the #N/A error value when it can't find G4 in column F. It returns an "Update" file dialog followed by #N/A if you refer to a worksheet that doesn't exist in the workbook containing the formula. It returns #REF! error value if the INDIRECT function isn't finding the worksheet. Based on this, my guess is that your data has problems--and that the regular VLOOKUP formula will fail to work as well.

Once you can demonstrate that the regular VLOOKUP formula works, the next step will be to add the INDIRECT part so you can copy it down as you originally asked.
Brad

 

[Mizzness]

Brad,

The vlookup works: =VLOOKUP(G5,'P&L Var Summary 03_02_05'!A,2,FALSE)

But the vlookup with Indirect doesn't.
=VLOOKUP(G5,INDIRECT("'P&L Var Summary "& TEXT(H5,"mm\_dd\_yy") & "'!A"),2,FALSE)


Am I missing something ? The formatting is fine.

Thanx for your patience.
Anthony

 

[AnotherHiggins]

Anthony,

Go to Tools > Add-Ins and check the box beside Analysis ToolPak.

The INDIRECT function is part of the analysis toolpak, and if you don't have it turned on Excel will return the #REF! error.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[Mizzness]

John,

GENIUS !!!

I never thought of that.

A star for solving the difficulty of simplicity.

Brad,

A star for patience well earned.


Thanx again.

 

[byundt]

Part of the Analysis ToolPak??? In which version of Excel is that?