Excel formula question - nested VLOOKUP?

So far, I have a VLOOKUP function that searches another sheet and pulls
the data from a cell matching a name located in row 4 of the same
column, and converts the result from a number of seconds to mm:ss.

Cell B27's contents (this checks the name in B4 and looks up their
number in col C on the sheet with data for that day):

=VLOOKUP(B4,'6-23-2004'!A1:C70,3,FALSE)/60/60/24

This works fine.

However, I have an array here.. it'll take me forever to write all this
out manually, and there's got to be an easier way.

Column A contains all the dates for the month (6-1-2004, 6-2-2004,
etc.)

How can I modify that formula so it looks on a worksheet (in the same
workbook is OK, i can hide the sheets) for that day? Each row is
related to one day, and each column related to one person.

I'd like it to go like..

=VLOOKUP(B4,A27!A1:C70,3,FALSE)/60/60/24

..and have it look in B4 for the name, A27 for the date (the worksheet
will be named the same as the date,) and then do the vlookup in A1:C70,
find the date in col C, etc.

Is that possible? Can I use a nested function to return the date
located in col A to search a worksheet by the same name?


---
Message posted from http://www.ExcelForum.com/

Hi!

Have a look at the INDIRECT() function.

Alf


---
Message posted from http://www.ExcelForum.com/

Hmm.. Maybe I'm not getting the syntax here?

Col A has a list of dates like so

(just text labels.. it would be easier to have actual mm-dd-yyyy labels
but I can't get it to display like "mm-dd-yyyy", only "mm/dd/yyyy" and
i can't use / in worksheet or workbook names)

6-1-2004
6-2-2004
...
6-30-2004

I have other worksheets named 6-1-2004, 6-2-2004, etc, with the
relevant data (names in Col A, numbers I pull in Col C)

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

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

I tried using INDIRECT in there a few ways, but can't get it to add up
right. I want to replace the worksheet name (which i have to type
manually right now - clumsy!!) with a function referring $A27, $A28,
etc, and using that text label to refer to the correct worksheet.

The formula checker says that A27 = 6-23-2004 as it should, but then
says that the formula result is Volatile, and then returns an error.

I can't seem to figure the syntax out... sorry if I'm being dense, it's
been a long day.


---
Message posted from http://www.ExcelForum.com/

"brightshadow " wrote...
Hmm.. Maybe I'm not getting the syntax here?

Col A has a list of dates like so

(just text labels.. it would be easier to have actual mm-dd-yyyy labels
but I can't get it to display like "mm-dd-yyyy", only "mm/dd/yyyy" and
i can't use / in worksheet or workbook names)

6-1-2004
6-2-2004
..
6-30-2004

?

You *CAN* format date entries as mm-dd-yyyy, but you may need to do so as a
custom number format.

I have other worksheets named 6-1-2004, 6-2-2004, etc, with the
relevant data (names in Col A, numbers I pull in Col C)

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

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

I tried using INDIRECT in there a few ways, but can't get it to add up
right. I want to replace the worksheet name (which i have to type
manually right now - clumsy!!) with a function referring $A27, $A28,
etc, and using that text label to refer to the correct worksheet.
...

If A27 were "6-23-2004" and A28 were "6-24-2004", try the following.

B27:
=VLOOKUP(B$4,INDIRECT("'"&A27"&'!$A$1:$C$70"),3,FA LSE)/60/60/24

then fill B27 into B28.

--
To top-post is human, to bottom-post and snip is sublime.

Hi!

See your other post

Alf


---
Message posted from http://www.ExcelForum.com/