Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

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


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

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?

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

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!


Have you...

Posted by Mark W. on March 14, 2001 2:27 PM
...looked at Excel's Data Consolidate... feature?

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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.