Linking spreadsheets and creating new lines of data

[JLenarcic]

I just finished creating a very large workbook with over twelve years of
daily market data. The first spreadsheet in the workbook contains
historical data of 16 market indicators. The next 15 spreasheets use
data from the 16 market indicators found on the first spreadsheet. I'm
trying to automate the process I go through daily updating the
spreadsheets with the most current data. For example, say tomorrow
morning I manually entered todays S&P 500 Closing Price in cell A4051
on the first spreadsheet. The second spreadsheet has several formulas
that use the number entered in cell A4051 of the first spreadsheet.
Right now, I highlight and drag down the previous days row to create a
new row of data using the most recent Closing Price value. Is there a
method or a formula that I can use that will automatically move the
formulas down one row in the second spreadsheet? Or will I always have
to manually highlight the previous day's formula and drag it down one
row to include the new data entered in the first spreadsheet? The goal
is to minimize the possibility of human error, so anything that atomates
this process in the slightest would be greatly appreciated. Thanks

 

[Paul Corrado]

You can use the Offset & Counta functions to always (as long as you are
consistent with your input format) pick up the last row of information

Assuming you wish to pick up the last number in Column A1 and assuming the
data starts in Row 1 and there are no blank spaces

=OFFSET(A1, COUNTA(A:A)-1,0)

PC