fred3
Member
This is a question about "archtiecture" a bit more than code I believe. I hope this is an appropriate place to ask.
I have a workbook of 3 sheets that I'm using as a "template" (not an Excel template yet .. just an .xlsm file). The file is replicated many times and gets different filenames. Then each get data in one sheet "Data".
Then, some of the important data points are brought into another sheet "Model" (simple Excel +[cell]) where we get two columns: Date and a Number in columns A and B.
In the "Model" sheet there are many calculating cells for each date in columns C >>CY
The default data set has 800 points. So, the base .xlsm file has 800 rows of calculations in the "Model" sheet.
And, at the top of each column there is a Heading in Row 1 and a summarizing number in Row 2 which is often the last number in the column taken using:
I only mention this because the last entry in the column has to be one that's meaningful.
In most cases there are well more than 800 data points and I'm only interested in the latest 800. That part works fine and I get 800 points. However, a problem in automating things arises when there are fewer data points available than the default 800. Let us take an example where the number of data points Date and Number transferred into Columns A and B in the "Model" sheet is 450 occupying rows 3-452.
There are *lots* of complex formulas in the cells. So I am very reluctant to change the cell contents in order to address this issue. I would currently delete rows 453-802 manually in the "Model" sheet in order to get a fully working file. This only needs to be done once but with many new files, it's an unwanted chore.
So, I'm looking for ideas regarding how to deal with this automatically.
I have a workbook of 3 sheets that I'm using as a "template" (not an Excel template yet .. just an .xlsm file). The file is replicated many times and gets different filenames. Then each get data in one sheet "Data".
Then, some of the important data points are brought into another sheet "Model" (simple Excel +[cell]) where we get two columns: Date and a Number in columns A and B.
In the "Model" sheet there are many calculating cells for each date in columns C >>CY
The default data set has 800 points. So, the base .xlsm file has 800 rows of calculations in the "Model" sheet.
And, at the top of each column there is a Heading in Row 1 and a summarizing number in Row 2 which is often the last number in the column taken using:
Code:
=+INDEX(J:J,COUNTA(J3:J802)+2)
I only mention this because the last entry in the column has to be one that's meaningful.
In most cases there are well more than 800 data points and I'm only interested in the latest 800. That part works fine and I get 800 points. However, a problem in automating things arises when there are fewer data points available than the default 800. Let us take an example where the number of data points Date and Number transferred into Columns A and B in the "Model" sheet is 450 occupying rows 3-452.
There are *lots* of complex formulas in the cells. So I am very reluctant to change the cell contents in order to address this issue. I would currently delete rows 453-802 manually in the "Model" sheet in order to get a fully working file. This only needs to be done once but with many new files, it's an unwanted chore.
So, I'm looking for ideas regarding how to deal with this automatically.