Hello
I have a question regarding 3d formulas, and Im not sure if excel can do
what Im thinking but I thought I would ask anyway because this program
surprises me all the time regarding what it can do.

Maybe Ill explain what Im trying to do just in case there is an easier way.
Basically Im building a template for a company to help them consolidate
their income statements.

So for example the company has an income statement with their revenues and
expenses for their US location on 1 tab, for their Canadian location on
another tab, etc.

I want to be able to add cell A3 from Sheet1 to cell A3 from Sheet2, etc,
but the added complication is I want the template to have the flexibility to
add new locations (ie. Add a new tab) without having to go through the whole
Consolidated statement and update every formula, because that would be a
pretty large job given the size of this template. (Also, doing it manually
would make the sheet prone to errors).

Im considering using a 3d formula, but these formulas make me quite
uncomfortable because If someone inserts a row on only 1 single sheet, it
wont consolidate properly anymore. Im thinking that if there is a way that
I can run an error check that would make me a lot more comfortable. I think
the best way to do this would be to refer to the header in the row. For
example if Cell A1 in each sheet has the header €œProfessional Fees€� and then
cell A2 in each sheet has the amount, I was think that I could run a check to
make sure that the header is matching on each sheet in the 3d formula before
i add the values in cell A2. Is there a way to do this?

Also, and this is may just be on my wish list, but it would be nice to
figure out a way to only consolidate certain worksheets. For example if I
want to see what the consolidated statement for Canada and the US looks like,
but I want to ignore the Australia statement in this particular scenario.
I was playing with the following formula which uses an array, but it doesnt
give me the flexibility to add new sheets easily. Do you have any ideas?

=SUM(N(INDIRECT({"Sheet1","Sheet2","Sheet5"}&"!"&A DDRESS(ROW(),COLUMN())&"")))

I was thinking if I could create some sort of inputs page where I can list
the sheets I want included in the consolidation and have that go into the
above formula where it says "Sheet1","Sheet2", etc. that would probably work
quite well.

If possible, I always try to avoid macros in templates because they tend to
intimidate the average user.

Thanks in advance for your help!

Here is an idea that may prove useful where ms is a defined name
={"Sheet1","Sheet2","Sheet5"}
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$B2:$B50"),A5,INDI RECT(ms&"!F2:F50")))

or this idea from RP using a list on sheet LU in col F

=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(L U!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNT A(LU!F:F))&"!A3")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brite" wrote in message
...
Hello
I have a question regarding 3d formulas, and Im not sure if excel can do
what Im thinking but I thought I would ask anyway because this program
surprises me all the time regarding what it can do.

Maybe Ill explain what Im trying to do just in case there is an easier
way.
Basically Im building a template for a company to help them consolidate
their income statements.

So for example the company has an income statement with their revenues and
expenses for their US location on 1 tab, for their Canadian location on
another tab, etc.

I want to be able to add cell A3 from Sheet1 to cell A3 from Sheet2, etc,
but the added complication is I want the template to have the flexibility
to
add new locations (ie. Add a new tab) without having to go through the
whole
Consolidated statement and update every formula, because that would be a
pretty large job given the size of this template. (Also, doing it manually
would make the sheet prone to errors).

Im considering using a 3d formula, but these formulas make me quite
uncomfortable because If someone inserts a row on only 1 single sheet, it
wont consolidate properly anymore. Im thinking that if there is a way
that
I can run an error check that would make me a lot more comfortable. I
think
the best way to do this would be to refer to the header in the row. For
example if Cell A1 in each sheet has the header €œProfessional Fees€� and
then
cell A2 in each sheet has the amount, I was think that I could run a check
to
make sure that the header is matching on each sheet in the 3d formula
before
i add the values in cell A2. Is there a way to do this?

Also, and this is may just be on my wish list, but it would be nice to
figure out a way to only consolidate certain worksheets. For example if I
want to see what the consolidated statement for Canada and the US looks
like,
but I want to ignore the Australia statement in this particular scenario.
I was playing with the following formula which uses an array, but it doesnt
give me the flexibility to add new sheets easily. Do you have any ideas?

=SUM(N(INDIRECT({"Sheet1","Sheet2","Sheet5"}&"!"&A DDRESS(ROW(),COLUMN())&"")))

I was thinking if I could create some sort of inputs page where I can list
the sheets I want included in the consolidation and have that go into the
above formula where it says "Sheet1","Sheet2", etc. that would probably
work
quite well.

If possible, I always try to avoid macros in templates because they tend
to
intimidate the average user.

Thanks in advance for your help!

Excel 2007 PivotTable
No formulas used,
ipso facto: No formulas to edit.
No code either.
Select multiple countries with drop-down list.
Easy to consolidate data in added sheets.
Ranges dynamic, can move freely.
http://www.mediafire.com/file/m04wzz...08_10_09a.xlsx