Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
I am trying to write a formula that includes a reference to the worksheet
name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
The formula I am using is =SUM('Day 1:Day 2'!L20)
but in the place of Day 2 I want the name of the worksheet to appear. Day 2 is the name of the worksheet! Do you mean that you want to enter the sheet name in a cell and have the formula use that cell reference? If so, are your sheet names really Day 1, Day 2 etc? -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... I am trying to write a formula that includes a reference to the worksheet name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
Yes, exactly......as new days are added and named (day 1, day 2 etc...just an
example) the sum formula needs to work from the first worksheet (day 1) to the last added worksheet. "T. Valko" wrote: The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. Day 2 is the name of the worksheet! Do you mean that you want to enter the sheet name in a cell and have the formula use that cell reference? If so, are your sheet names really Day 1, Day 2 etc? -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... I am trying to write a formula that includes a reference to the worksheet name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
Try this:
Sheet names are Day 1, Day 2, Day 3, etc. A1 = 3 =SUMPRODUCT(SUMIF(INDIRECT("'Day "&ROW(INDIRECT("1:"&A1))&"'!L20"),"<1E100")) That is the same as: =SUM('Day 1:Day 3'!L20) If A1 is empty you'll get a #REF! error. If you enter a number in A1 and that sheet doesn't exist you'll get a #REF! error. -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... Yes, exactly......as new days are added and named (day 1, day 2 etc...just an example) the sum formula needs to work from the first worksheet (day 1) to the last added worksheet. "T. Valko" wrote: The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. Day 2 is the name of the worksheet! Do you mean that you want to enter the sheet name in a cell and have the formula use that cell reference? If so, are your sheet names really Day 1, Day 2 etc? -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... I am trying to write a formula that includes a reference to the worksheet name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
Note that in the formula there is a space character between Day and "&ROW:
....Day "&ROW... Sometimes line wrap will break at those space characters and will make it look like there is no space in the formula. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Sheet names are Day 1, Day 2, Day 3, etc. A1 = 3 =SUMPRODUCT(SUMIF(INDIRECT("'Day "&ROW(INDIRECT("1:"&A1))&"'!L20"),"<1E100")) That is the same as: =SUM('Day 1:Day 3'!L20) If A1 is empty you'll get a #REF! error. If you enter a number in A1 and that sheet doesn't exist you'll get a #REF! error. -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... Yes, exactly......as new days are added and named (day 1, day 2 etc...just an example) the sum formula needs to work from the first worksheet (day 1) to the last added worksheet. "T. Valko" wrote: The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. Day 2 is the name of the worksheet! Do you mean that you want to enter the sheet name in a cell and have the formula use that cell reference? If so, are your sheet names really Day 1, Day 2 etc? -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... I am trying to write a formula that includes a reference to the worksheet name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
refence worksheet name into a formula
You can make your Sum() formula dynamic, where it totals all the sheets in
the WB, including any new ones. Insert a "dummy" WS at the beginning of your sheet tabs (extreme left) and name it Start Then insert another at the extreme right and name it End. Now, change your formula to: =Sum(Start:End!L20) Insert every new sheet *between* the "sandwich" of those 2 dummy sheets, and your formula will *automatically* include those new sheets in the total. You can even play "What If", by moving sheets in and out of the sandwich, to see different totals in different scenarios. Also, if you wish, you can hide the dummy sheets and the formulas will still work. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "excelhurtsme" wrote in message ... Yes, exactly......as new days are added and named (day 1, day 2 etc...just an example) the sum formula needs to work from the first worksheet (day 1) to the last added worksheet. "T. Valko" wrote: The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. Day 2 is the name of the worksheet! Do you mean that you want to enter the sheet name in a cell and have the formula use that cell reference? If so, are your sheet names really Day 1, Day 2 etc? -- Biff Microsoft Excel MVP "excelhurtsme" wrote in message ... I am trying to write a formula that includes a reference to the worksheet name. The formula I am using is =SUM('Day 1:Day 2'!L20) but in the place of Day 2 I want the name of the worksheet to appear. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup , Referencing a cell to refence table array | Excel Worksheet Functions | |||
Copying a formula from one worksheet to another worksheet | Excel Worksheet Functions | |||
How do I take a cell in one worksheet and add it into the formula in a different worksheet in the same workbook? | Excel Worksheet Functions | |||
Allow refence in the "table_array" position of Lookup functions | Excel Worksheet Functions | |||
end of column refence in several calculations | Excel Discussion (Misc queries) |