Allowing formula to follow an item rather than the worksheet line!

Posted by Gavin on March 14, 2001 3:26 AM

Hi, I hope someone can help and that this is not too basic!

I have a spreadsheet with multiple worksheets.
Each worksheet represents a region with say 30 products and quantities.

One 'master' worksheet contains all the accumulated totals of the other worksheets.

My issue is that I have a basic formula for the accumulations, but the cells in the formula correspond with the worksheet line rather than the product.

This means that if I delete/add a product line to a 'region' worksheet it throws all the fomula out in the accumulated worksheet!

Any ideas

Thanks

Gavin

Posted by Mark W. on March 14, 2001 6:37 AM

Gavin, why don't you consolidate all of your
regional data into one list and then use a
PivotTable to summarize it?

Posted by Gavin on March 14, 2001 11:58 AM

Because its put onto an FTP site and each regional manager has access only to their worksheet!

Posted by Mark W. on March 14, 2001 2:27 PM

Have you...

...looked at Excel's Data Consolidate... feature?

Posted by Dave Hawley on March 14, 2001 11:58 PM

Hi Gavin

When you say "delete/add" does this mean your are deleting the entire row and adding entire rows ?

What type of formulas are you refering to and are you using relative or absolute references ?

Have you tried naming the ranges ?

Sorry about raising more questions than you have asked, just trying to get a clear picture.

Dave

OzGrid Business Applications