Need help with formula syntax

[DStrong]

I am usually very good at figuring these out but cannot for the life of me
get this one.

First off I am using Excel 2003 on a XP Pro machine.

I have a workbook with 30 sheets in it. 23 of these are for working days of
the month. They are named in the following fashion:

May01, May04, May 05, etc.

I have another sheet that I want to have calculate the totals from each
daily page. I also want this to be dynamic in the formulas so when the next
month comes around I do not have to spend a great deal of time setting up the
formulas. I have the same daily sheet names in a header (row B) on my totals
sheet. So I have tried to write a formula that is something like this:

=B2!L34

B2 currently equals May01 and L34 is the place on the daily sheets that
contains the total I need to place on my totals sheet.

I found a function called INDIRECT that is supposed to take a cell's text
and use it as a part of the formula, but keep getting REF when I try this
out.

This all seems to be just a syntax error and I am overlooking it. PLEASE HELP.

-David

 

[Eduardo]

Hi,
See Debra web, has an excellent explanation

http://www.contextures.com/xlFunctions05.html

If this helps please click yes, thanks

 

[Jacob Skaria]

An example.

A1 = "Sheet1" (without quotes)
In A2 enter the below formula
=INDIRECT(A1 & "!B1")

This will return cell B1 of sheet1. Now change A1 to sheet2. the formula
will return B1 of Sheet2

If this post helps click Yes

 

[DStrong]

OK, I think that I left one very critial part out. My header row that lists
the names of the sheets, is dynamically filled in based on a date. It is
formated with the MMMDD custom formatting. The cell is actually a date in
excels numeric format behind the scenes. I think that do to this is why I an
getting the result of #REF! as my formula result.

Is there any way to get around this? The header formula is 'Sheetname'!A4
which contains a date custom formatted to display MMMDD (May01, May02, etc.)

 

[Jacob Skaria]

In the below example

A1 = "Sheet1" (without quotes)
In A2 enter the below formula
=INDIRECT(A1 & "!B1")

If A1 is a date and you want to custom format it to mmmdd try the below
formula

=INDIRECT(TEXT(A1,"MMMDD") & "!B1")

Try and feedback

If this post helps click Yes