Extending formulas/formats with imported tata

[Charles Lavin]

Hi --

I have a worksheet that includes imported data. Several columns consist of
row-specific formulas. How do I get Excel to automatically adjust these
columns to account for more or fewer rows whenever I import new data?

Example 1: I import a file with 100 rows. I create the formulas on columns
outside the imported data and copy these formulas down the 100 rows. The
following week, I call up this spreadsheet, and using Data | Import External
Data | Edit Text Import, I import a file with 145 rows. The formulas on the
first 100 rows recalculate properly. But the final 45 rows don't have
formulas at all. How do I get Excel to automatically extend these formulas
down the 45 new rows?

Example 2: I import a file with 100 rows. I create the formulas on columns
outside the imported data and copy these formulas down the 100 rows. The
following week, I call up this spreadsheet, and using Data | Import External
Data | Edit Text Import, I import a file with 72 rows. The formulas on the
72 rows recalculate properly. Additionally, I have 28 otherwise empty rows
with calculated numbers in these columns. The numbers are either 0 or some
calculation error (depending on what the formula is doing). How do I get
Excel to delete these superfluous 28 rows?

Thanks,

CL

 

[Pete_UK]

One way of doing it it to ensure that your formulae are copied down to
cover more rows than you anticipate (eg to row 300). You can amend
your existing formulae along the lines of:

=IF(A1="","",your_existing_formula)

i.e. if there is no data then return "", otherwise do the calculation.

Hope this helps.

Pete

 

[Craig Schiller]

Can you say "imported tata" in a public newsgroup?

<BG>