find last row of data and dynamically change formula in that row

I've built a workbook as a template to analyze sets of data. The data is a time series of stock prices, with no definite start or end date.

One workbook may have 1,100 rows, while the next may have 631 rows.

In each workbook, the pasted data fills columns A through D in a worksheet.

Columns E through Z in the same worksheet contain formulas dependent on the data in A:D. These formulas make up the "template" which I use to crunch the numbers.

Here's the challenge: Each time I create a new workbook and paste new data, there is always one cell in one column (H) in which I have to manually change the formula, so that it refers to a different cell than the formulas in the rows above.

Is there a way to paste data into a worksheet then have the worksheet apply this "fix" to the last row containing a number in column H?

example: there are 1150 rows of data. Formula H1 through H1149 is =max(A$1:A1149). But the formula for H1150 needs to be "=F1149"

Please note that once the data is pasted in A through D, my task is just beginning. I have lots of analysis to do, where the results are dependent on the pasted data and various adjustments on my "dashboard." So I don't think pivot tables will work. And, because I'm using solver as part of my analysis in which it performs thousands of iterations to arrive at an optimal result, I prefer not to use arrays, which I understand require lots of CPU power.

Its fine with me to create helper columns or worksheets.

Please let me know if I need to clarify, or post a sample worksheet.

Thanks so much for your help.