How to improve workbook structure?

[fred3]

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:

=+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.

 

[Luke M]

Sounds like you need to setup some sort of dynamic range. We need to look for last row, and take either the latest 800 data points, or all of them if < 800 points? This then would define your range:
=INDEX(J:J,COUNTA(J:J)):INDEX(J:J,MAX(3,COUNTA(J:J)-800))
The MAX function will make sure that we don't cause an error if < 800, and if we have more than 800, the subtraction will move us further down the column.

 

[fred3]

Thank you Luke M!
There's never more than 800. That's already taken care of in the transfer into the "Model" sheet.
The problem is there isn't a "last row" in columns C>>CY, there are always 802 in the "template" file. And there needs to be a meaningful last row (the formulas past it need to be deleted).
But, the number of rows is known and columns A and B end at the right place already.
So, what I need is something that will do this:
- Start with the last row number N.
- If N<802 Delete rows N+1 to 802.
So, if N=452 then delete rows 453 to 802.

 

[Luke M]

You're looking for a macro then? Or a dynamic range (via formulas)?
The formula I gave you is answer for solution. That lets you keep your formulas in place, but only refer to the cells of interest (ignoring the extra rows). If you want a macro to physically delete the extra cells:

Sub KillExtra()
Dim lastRow As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range(lastRow + 1 & ":" & .Rows.Count).EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub

 

[fred3]

Luke M, thank you again.

I guess I was thrown off a bit in that columns like "J" have 802 filled rows in each column.
So I don't yet see how that ignores the other rows...?
It's only columns A and B that have filled rows only down to the correct number.
So, I guess I would want to create a table that is based on column A and applies to columns C>CY?
I believe I'd rather do it this way than with VBA.

 

[Luke M]

Hi fred,
Now I'm the one confused. I've presented both a formula and a VB solution...I'm not sure if you've tried either of them, or what went wrong with them. Perhaps you could upload an example workbook, showing what your data layout looks like, and what ultimately you are trying to achieve?

 

[fred3]

Thanks for sticking with me on this Luke M!
I think I've got a solution now.
I was unclear about dynamic ranges and what your total suggestion was. So, I took your advice and tried it on a small example worksheet.
I set up columns A and B as the data columns which will have variable length (i.e. they are filled to a point and then have nothing but blanks thereafter).
I set up columns C and D as the "fixed" formulas over a fixed number of rows (I used 20 rows for this example).

I made a wild guess and set up a named range that refers to the entirety of columns C and D from row 3 to row 22 like this:

=OFFSET(Sheet2!$C$3,0,0,COUNTA(Sheet2!$D$3:$D$22),1)


The hope was that one would not have to create separate names with dynamic ranges for each column (since the real sheet has a lot of columns).
This seems to work.

The dynamic range takes care of picking up values in columns A and B but I remain mystified what there is in that entire construct that does that!!

Then, at the top of column C, in row 2, I put:

=INDEX(C:C,COUNTA($A3:$A22)+2)

.. and similarly in column D.

The contents of row 2 have to be keyed to a COUNTA on column A (or B) in order to pick the final nonzero value out of columns C and D.

If you see any awkwardness in how I've done it, I'd appreciate your comments. The example spreadsheet is attached.

Thanks again!!

 

[fred3]

I'm sure that I don't understand something here. I see no reason for the dynamic range at all as it appears it's not being used. The row 2 summaries work without it - so, they pick up the appropriate values in the columns.
But I do have charts that need to be updated and I've followed the OFFSET formula method and have it working except it won't update when the data changes.

 

[Luke M]

Hi fred,

Take a look at the attached. I created a full set of dynamic ranges, and plugged them into a chart. Hopefully this illustrates how the ranges work, and the benefit that can occur.
It might also be helpful to take a quick read of this tutorial, if you want to know more about how I set it up. Tutorial explains how to do last 12 months, but you can see how the two ideas would be related.

 

[fred3]

Thanks! The example did the trick. Now I get what I was missing re the base for the offset.
The thing is working well now. So much easier to deal with!!

 

[fred3]

So far, so good. Now I'd like to be able to insert columns (as at your Column C) and have the chart remain.
I've tried this:
OFFSET_CHART_MODEL=OFFSET(RangeBounds_of_A,0,COLUMN()+Model!BX3)
but the BX3 cell reference keeps changing. Like it becomes BN1 for example and may even continue to change, I've not tested it further.

I also tried doing this:
In cells BV3, BW3, BX3 I put the column offset needed: 73, 74 and 75.
Then I used:
OFFSET_CHART_MODEL=OFFSET(RangeBounds_of_A,0,Model!BX3)
and, again, the BX3 part of the expression changes to something like S3.
Model is the name of the sheet and automatically gets inserted.

 

[Luke M]

Need to make the reference absolute, otherwise it will change as you select different cells. E.g.,
OFFSET_CHART_MODEL=OFFSET(RangeBounds_of_A,0,Model!$BX$3)