Need help with formula syntax
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
---------------
Jacob Skaria
"DStrong" wrote:
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.)
--
David
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"DStrong" wrote:
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
|