importing data and formulas into Excel

[Eric]

Situation 1: I want to import data from a .CSV or .TXT into Excel,
and I'd like to include formulas in these import files and have Excel
properly execute the formulas. Can this be done? If so, how do you
get the formulas written properly in the import file?

Situation 2: As an alternative to the above situation, I have an
existing Excel spreadsheet with formulas. I'd like to import data
from a .CSV or .TXT file each month to this Excel spreadsheet,
replacing the data with new data, but retain the formulas in my Excel
spreadsheet. Is this possible to do? If so, how's the best way to
do it? (One problem is the import files will vary from month to
month, as far as how much data is included. That will make the cells
containing the formulas be in the wrong places from month to month.)

 

[David DeRolph]

I believe Situation 2 is the best approach because I don't think you can
import formulas as you described in Situation 1.
It seems to me Situation 2 would work well if you simply set up areas for
importing data and make these areas large enough to accomodate the maximum
number of records you are likely to ever import. In other words, you would
often have some blank rows. Would that be problem?

 

[Dave Peterson]

You an import formulas and excel will see them as formulas.

I put:

10 21 33 34 1 99 =SUM(RC[-5]:RC[-1])
6 12 26 2 30 76 =SUM(RC[-5]:RC[-1])
17 13 24 3 8 65 =SUM(RC[-5]:RC[-1])
35 22 20 5 11 93 =SUM(RC[-5]:RC[-1])
4 25 29 9 23 90 =SUM(RC[-5]:RC[-1])
28 31 32 7 16 114 =SUM(RC[-5]:RC[-1])
14 18 27 19 15 93 =SUM(RC[-5]:RC[-1])

in a text file. I switched to R1C1 reference style--since it made the formulas
a little more robust.

Excel brought them in ok.

If I had A1 reference style, I needed to have that in my text file, too.

But I wouldn't do this either. You'd have to do something pretty neat to save
the formulas in a .txt file or .csv file.

But you could record a macro when you imported your text file manually.

Keep recording the macro when you insert columns, add formulas, fill them down,
add headers, all that stuff.

And a hint. If your files are named *.csv, then rename them to *.txt. Excel's
VBA ignores any of your settings during the import when the file is named .csv.