B
Brite
Hello
I have a question regarding 3d formulas, and I’m not sure if excel can do
what I’m thinking but I thought I would ask anyway because this program
surprises me all the time regarding what it can do.
Maybe I’ll explain what I’m trying to do just in case there is an easier way.
Basically I’m 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).
I’m considering using a 3d formula, but these formulas make me quite
uncomfortable because If someone inserts a row on only 1 single sheet, it
won’t consolidate properly anymore. I’m 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 doesn’t
give me the flexibility to add new sheets easily. Do you have any ideas?
=SUM(N(INDIRECT({"Sheet1","Sheet2","Sheet5"}&"!"&ADDRESS(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!
I have a question regarding 3d formulas, and I’m not sure if excel can do
what I’m thinking but I thought I would ask anyway because this program
surprises me all the time regarding what it can do.
Maybe I’ll explain what I’m trying to do just in case there is an easier way.
Basically I’m 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).
I’m considering using a 3d formula, but these formulas make me quite
uncomfortable because If someone inserts a row on only 1 single sheet, it
won’t consolidate properly anymore. I’m 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 doesn’t
give me the flexibility to add new sheets easily. Do you have any ideas?
=SUM(N(INDIRECT({"Sheet1","Sheet2","Sheet5"}&"!"&ADDRESS(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!