excel - nesting INDIRECT inside a VLOOKUP?

[brightshadow]

Is it possible to nest an INDIRECT function inside of a VLOOKUP?

Here's what I've got so far:

Cell A27: 6-23-2004

Cell B27:
=VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24

I want to replace '6-23-2004' with INDIRECT($A27) so I can autofil
this formula across a large worksheet, and so when I adjust the dat
range, it will dynamically update the information PROPERLY according t
the name (row 4 of same col) and date (col A of same row, referring t
a separate worksheet in the same workbook by that name) in th
formula.

I can't seem to get the syntax right. Is there a right syntax? I'm no
just trying to do something that isn't possible, am I? :confused

 

[AlfD]

Hi!

You've moved (or, worse still, double-posted?)

1.=INDIRECT("'"&A1&"'"&"!"&"B1") will read a sheet name from A1 of th
active sheet and then find the value of B1 on that sheet, provided th
sheet exists.

2. You can format dates like 06-23-2004 by using format >cells > numbe

custom and type mm-dd-yyyy in the box labelled "Type".

3. You can enter 06-23-2004 in a1: format A1:A30 as above: drag A
down by its drag handle and get a month's supply of dates.

Al

 

[brightshadow]

Sorry about the double post, I was having some major problems gettin
the page to load in the last 48 hours.

I understand the cell formatting and the autofill functions.. I'
getting hung up on the syntax for nesting the INDIRECT inside of th
VLOOKUP.

When I tell INDIRECT to look at A27, which has 6-23-2004 in m-d-yyy
format, it reads it as 38161. (Will that still work?)

=VLOOKUP(B$4,(=INDIRECT($A27,TRUE))!$A$1:$C$70,3,FALSE)/60/60/24

^-- This doesn't work.. can I put the VLOOKUP inside the INDIREC
instead of vice versa somehow? I guess that doesn't make any sense, bu
I'm unclear on how the syntax should look here. Does the above line ge
the idea of what I'm trying to accomplish across, at least?

(Clarification - I only want to use the INDIRECT function to return th
cell on the *same* sheet that is named identical to the othe
worksheet, so it will return the "6-23-2004" string in the middle o
the VLOOKUP formula.. so I can autofill that formula and not have t
change anything but the dates in col A.)

 

[AlfD]

Hi!

Your INDIRECT doesn't exactly look like mine.

Build it a bit at a time. Get the "indirect" function working first. I
will then generate a value you can (in principle) use in a vlookup.

Al

 

[brightshadow]

Ah ha! I couldn't get INDIRECT to work the way I wanted it to, but
found a simple way around it (labelled the sheets 1, 2, 3, etc b
date.. i'm importing the data manually anyway, so this is viable) an
now have a populated worksheet.

Another silly newbie-ish question: The formula returns #N/A when n
data is present (this is common) -- is there an easy way to make it no
display anything when #N/A is the formula result?

*dons his dunce cap

 

[brightshadow]

(nevermind, i figured that out.. first time the help files were actuall
clear enough to be useful! go excel!

 

[AlfD]

Hi!

Good for you!

Al