G
Gene
I have several Worksheets. Up to 75. What is wrong with the following
reference.
=ABX:XLNX!J4
reference.
=ABX:XLNX!J4
M
Mike H
Hi,
ABX:XLNX is an illegal sheet name because of the semi colon.
Mike
ABX:XLNX is an illegal sheet name because of the semi colon.
Mike
G
Gleam
When I try naming a sheet ABX:XLNX I get an error message - sheet names
cannot have a colon in them.
cannot have a colon in them.
G
Gene
My understanding is that a colon is necessary to represent the first and last
sheet. It is placed automatically in the naming of a formula. But you are
right that it is an invalid sheet name. Look at the instructions for
referencing the same cell in several sheets. You are told to select the
first sheet, hold SHIFT and select the last sheet. The colon is placed
automatically but unfortunately it doesn't work
sheet. It is placed automatically in the naming of a formula. But you are
right that it is an invalid sheet name. Look at the instructions for
referencing the same cell in several sheets. You are told to select the
first sheet, hold SHIFT and select the last sheet. The colon is placed
automatically but unfortunately it doesn't work
D
Dave Peterson
Wouldn't you want something like:
=sum('abx:xlnx'!J4)
to sum those up to 75 cells
=sum('abx:xlnx'!J4)
to sum those up to 75 cells
G
Gene
Specifically what I have is indirect("f"&left(abx:xlnx!j14,3)) but in order
to find why I had the error I tried =abx:xlnx!j14 in a cell by itself and got
the same error. Did it work in the SUM function?
to find why I had the error I tried =abx:xlnx!j14 in a cell by itself and got
the same error. Did it work in the SUM function?
G
Gene
i TRIED W/ sUM AND FOUND THAT IT WORKED. I still don't know why it didn't as
a stand alone. I am using my formula in a name function, apparantly
incorrectly. Thanks anyway, perhaps I have a clue as to how to use it for my
purpose
a stand alone. I am using my formula in a name function, apparantly
incorrectly. Thanks anyway, perhaps I have a clue as to how to use it for my
purpose
H
Herbert Seidenberg
There are only a few functions that work with 3-D reference, like
SUM, COUNT, AVERAGE, MAX and some statistical functions.
INDIRECT and [Blank] do not work. Check list in Help.
However, if your sheets have sequential numbers, like
Page1, Page2, etc
there is a workaround.
Assume you want to sum the range A1:A6 on Page01 thru Page03,
B1 contains the text "ages" and the named range Set1 has the formula
={1;1;1;1;1;1}
and you want to play games with the text "Page"
then you can use this formula:
=SUMPRODUCT(SUMIF(Set1,1,INDIRECT("P"&LEFT(B1,3)&
TEXT(ROW(INDIRECT("1:3")),"00")&"!A1:A6")))
SUM, COUNT, AVERAGE, MAX and some statistical functions.
INDIRECT and [Blank] do not work. Check list in Help.
However, if your sheets have sequential numbers, like
Page1, Page2, etc
there is a workaround.
Assume you want to sum the range A1:A6 on Page01 thru Page03,
B1 contains the text "ages" and the named range Set1 has the formula
={1;1;1;1;1;1}
and you want to play games with the text "Page"
then you can use this formula:
=SUMPRODUCT(SUMIF(Set1,1,INDIRECT("P"&LEFT(B1,3)&
TEXT(ROW(INDIRECT("1:3")),"00")&"!A1:A6")))
P
Peter Scott
I found this thread on an exhaustive search but it and others like it
haven't yet answered my question so I will have to ask.
I have a worksheet, call it 'Summary', where I want each cell in column B
to be set to the sum of the cell in the same position in all worksheets
named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 =
S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ...
The number of S<digit> sheets will change from time to time (although
they will always be contiguous starting at 1).
I have these requirements:
1) I do not want to have to change the content of the cells in Summary
column B whenever that happens. (I may be doing the same thing with
additional columns.)
2) I will add and subtract rows from column B periodically and do not want
to have to edit the formulas in the summary column when that happens. I
want to be able to just copy and paste the formula into a new cell when
expanding the length of the column.
I can put the names of the S<digit> worksheets in cells or a name and
change that whenever the worksheets change.
This has to work on Excel V.X for Mac in addition to Office 2003 for PC.
If you know the answer you can stop here; the rest is just to prove I
have tried
I have a long list of things that have not worked.
Obviously
=SUM(S1:S8!B1)
satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results:
=SUM(INDIRECT(X1)&"!B1")) ... #VALUE
{=SUM(INDIRECT(X1)&"!B1"))} ... #REF
{=SUM(SUMIF(INDIRECT(X1)&"!B1"),">0")} ... #REF
Putting 'S1', 'S2', ... 'S8' into X1:X8 and using
{=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),">0")}
works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting
rows.) For some reason I need SUMIF to get anywhere because if I try
{=SUM(INDIRECT(X1:X8)&"!B1")}
I get only the value of S1!B1. ">0" is an acceptable condition. Now,
{=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),">0")}
works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge
against all future increases I get #REF when there is nothing in X9.
{=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),">0"))}
where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1.
Defining SheetRange as 'S1:S8' and trying
=SUM(SheetRange!B1)
gives #REF. I've attempted some things with double INDIRECTs that are too
embarrassing to post here. Help...!
haven't yet answered my question so I will have to ask.
I have a worksheet, call it 'Summary', where I want each cell in column B
to be set to the sum of the cell in the same position in all worksheets
named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 =
S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ...
The number of S<digit> sheets will change from time to time (although
they will always be contiguous starting at 1).
I have these requirements:
1) I do not want to have to change the content of the cells in Summary
column B whenever that happens. (I may be doing the same thing with
additional columns.)
2) I will add and subtract rows from column B periodically and do not want
to have to edit the formulas in the summary column when that happens. I
want to be able to just copy and paste the formula into a new cell when
expanding the length of the column.
I can put the names of the S<digit> worksheets in cells or a name and
change that whenever the worksheets change.
This has to work on Excel V.X for Mac in addition to Office 2003 for PC.
If you know the answer you can stop here; the rest is just to prove I
have tried
I have a long list of things that have not worked.Obviously
=SUM(S1:S8!B1)
satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results:
=SUM(INDIRECT(X1)&"!B1")) ... #VALUE
{=SUM(INDIRECT(X1)&"!B1"))} ... #REF
{=SUM(SUMIF(INDIRECT(X1)&"!B1"),">0")} ... #REF
Putting 'S1', 'S2', ... 'S8' into X1:X8 and using
{=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),">0")}
works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting
rows.) For some reason I need SUMIF to get anywhere because if I try
{=SUM(INDIRECT(X1:X8)&"!B1")}
I get only the value of S1!B1. ">0" is an acceptable condition. Now,
{=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),">0")}
works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge
against all future increases I get #REF when there is nothing in X9.
{=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),">0"))}
where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1.
Defining SheetRange as 'S1:S8' and trying
=SUM(SheetRange!B1)
gives #REF. I've attempted some things with double INDIRECTs that are too
embarrassing to post here. Help...!