I have a rather huge Excel sheet where the first 20 columns contain input data, further 15 columns contain formulas which use the input data for calculations.
I frequently remove the entire input data set and replace it with a new one, the input data volume varies and ranges anywhere from 4.000 to 10.000 rows.
Is there support in Excel to auto-adjust the formula part to the number of input data rows? I do that manually now, either deleting rows if the input data is smaller than the previous set or copying formula cells if the input data became larger. This is quite tedious, so I am looking for a way to automate this.
Some of the formulas produce #N/A if no input data is present and I condense the calculated data with pivot tables, so I need the formulas exactly matching the input data rows.
I am on Excel 2003.