Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula syntax
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula syntax
Hi,
See Debra web, has an excellent explanation http://www.contextures.com/xlFunctions05.html If this helps please click yes, thanks "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula syntax
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula syntax
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula syntax. | Excel Discussion (Misc queries) | |||
Syntax of formula | Excel Worksheet Functions | |||
Formula Syntax | Excel Worksheet Functions | |||
Formula syntax {;;;} | Excel Worksheet Functions | |||
Syntax For Conditional Formula | Excel Worksheet Functions |