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

[Shannon]

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!

 

[David Biddulph]

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?

I'm confused as to how you are getting the #REF. I can paste new data into
sheet 1 and the references in sheet 3 remain intact.

As a matter of interest, do you get the same result if you Paste Special/
Values as you get when you just use Paste?

 

[Shannon]

Actually -- I didn't explain it correctly the first time... I import
the data from a CSV.

But in explaining it incorrectly, it led to you giving me and idea. I
will try to import the data into another book and the copy/paste
values into the "source" sheet of the main book..

Thanks for the idea...


Shannon

 

[Guest]

I import CSV data from an instrument and use macro keys to delete everything
that is to the right of the fourth column. In the first three columns are
macro buttons which replicate the formulas (assuming that the same data is
in the same locations every time. The macro buttons clear out data, perform
calculations, prepare graphs, etc. I used the recorder to create the macros
and paste them into the buttons.

Paul