Is there a way to keep the formulas and change their source data?

Giganews Newsgroups
Subject: Is there a way to keep the formulas and change their source data?
Posted by:  Shannon (shannon@nospam.com)
Date: Sat, 20 May 2006

Hello, thank you in advanc for taking the time to read this.

I have several worksheets that I use to extract data from various CSV
file imports and manipulate it into the form  I need for my work.

This saves a  boatload of time over hand-extracting the data from
several sources and typing it into Excel.

HOWEVER.  Let's say for example I have 100 items I need to extract
data from.

Sheet1 is a 100-item CSV import
Sheet 2 is another 100-item CSV import (from another source with other
data relative to the same 100-item list)

Sheet3 is the real"worksheet" that includes both the extracted data
from sheets 1 & 2 and the calcs I need to use in my report.

Say sheet3!a:10 is something like =sheet1!a:35+sheet2!b37

in this case, the cell would have the value of $500.00

Now, say EVERY TIME I do a new report, I need to use different
100-item CSV files in sheets 1 and 2.  But the data is in the same
format, so the calcs on sheet 3 would work.

sheet3!a:10 would still encounter nuimbers (though different) at
=sheet1!a:35+sheet2!b37, and the NEW figure should be say $480.00

BUT it doesn't work that way.  Because when you copy over the old
100-item lit with a new 100-item list, all the calcs on sheet3 go to

#REF!

and you have to re-do all your calcs.

Is there any way to get Excel to act on the cell reference rather than
content and so be able to paste in new data (which might, by the way,
have the same value entirely) so that I can use  sheets 1 and 2 as
true data source sheets without re-doing all the calcs in sheet 3?

Thanks gin in advance!

Replies