Indirect References - Summarizing sheets - Relative?

[Guest]

Hi all
- Hoping anyone could help on this problem I am seeing.
I have a workbook called SummaryForecast and have several
sheets in the book: Summary; DeptA; DeptB; DeptC etc.
The summary sheet takes data in 5 categories from each
Dept and then sums to a Dept total.

eg-
DeptA
Category 1 45,000
Category 2 23,000 etc...
....
Category 5 12,000
Total DeptA 123,000.

In order to get this information, I email the Dept sheets
to people, they update the detail behind each category
(which may include adding/subtracting line items) and then
I copy the tab (making sure the name is the same) back
into the original workbook. Whew. Are you still with me?
So what is supposed to happen is that all the links to the
numbers from each category should be updated. I have this
with =indirect(DeptA&"!g5") and it works as long as no
line items have been added or subtracted. If items have
been added, this formula is inaccurate.
So - finally- what i would like to do is have the formula
on the summary sheet update the category information,
regardless if items have been added or not.
Any help you can give would be really appreciated!! thanks!

 

[Frank Kabel]

Hi
you may provide a little bit more detail about the layout of your
department sheets. I think using SUMIF in combination with INDIRECT
should do