Dynamic Data Computation "HELPER" Table Question ...

[Sunny54321]

Is there a way to avoid having a LONG partially blank data calculation “helper” table that is filled with formulas?

Any suggestions for creating a “dynamic” length calculation table that avoids the need for a table that is “formula heavy”?

I have created a non-dynamic data computation table (“helper table”) that processes a large data file to summarize information by “DAY”. Each day has 24 points of data (24 hours). The number of rows can be between 1 month to 3 years. Each cell has a fancy formula to harvest selected criteria and SUMMARIZE data for each day.

Currently, I “DRAG” the 24 point wide set of FORMULA cells to match the date Range.
Is there a way to do this dynamically, have this helper table increase or decrease in length to avoid having a preset LONG blank series of data cells that may not be used?

For example, a helper table can be created to display 4 month performance by Zone 3/Type 5 stuff. The table would be (4 times 30 = 120 days) 120 rows long and 24 columns wide (24 hours/day).

But if I want to increase my output info window to 2 years, is there a way to have it remain “blank” and automatically (or upon an increase of dates listed) copy the calculation formulas down? Or is the only way to have a large 2 year table with formulas that would always be partially not-utilized?

Any insight is welcome,

Thanks,
Sunny

 

[sijpie]

This is pretty easy to achieve.
What you want is a macro that runs to fill out the formulas when you make a change to a particular range.

Say that your dates are in column A. And the formulas are in column B and C. If you put a date (or any change) in a cell in column A the macro will fill out the formula in the same row in cells B & C.

So how do we do this?
The Excel Worksheets objects can capture events, and one of the events is changes to the sheet. This is what we will use.
Open a new workbook for this demo. Rename the first sheet 'Auto'
We need to access the code sheet for the worksheet object. This can be done from the macro editor by double clicking on the sheet name, but even easier is to right click on the sheet name tab in Excel and select 'View Code'.

This will open the macro editor in the correct code module. Note: these modules are not the standard modules and should not be used to write normal macros in.

just above the big right hand panel of the VB editor you see two dropdown lists. click the left one and select Worksheet from the list. A empty macro appears below (Worksheet_SelectionChange). In the right hand dropdown you can now find a number of events that the worksheet can capture. One of them is Change. This is the one we need, so click on it. The VBE will write its skeleton macro in the pane. OK, delete everything and copy the following into the editor:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("A")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'the change is in column A</SPAN><br>        <SPAN style="color:#007F00">' enter the required formulas in cells B and C on the same row</SPAN><br>        Cells(Target.Row, 2).Formula = "='Main'!A" & Target.Row + 2 & "*2"<br>        Cells(Target.Row, 3).Formula = "='Main'!B" & Target.Row + 2 & "*4"<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Now go to excel and create another sheet which you rename 'Main'. Fill some values into columns A and B of this sheet.
Now go back to the Auto sheet and enter something in a cell in column A

Ok so what does it do:
Excel captures your keypresses and sends them to the active sheet (in this case sheet Auto). When you press enter, the sheet makes the changes and runs the change macro.
This macro gets as an argument 'Target'. Target is the cell(s) being changed.
The macro then first checks to see if target is in column A, we did this with the Intersect function, which checks if there is an 'intersection' between the target and column A. If so then we add the formulas in cells B & C of the same row.

Play with it and see how you can modify it for your formulas.

Once you have that correct you will need to adapt it for the case where you copy down more than one row of dates. I can help with that, just post a reply here.