Dynamicly copying down formulas (1 Viewer)

[laxster]

I am having an issue with file sizes due to a complex series of formulas that I'm trying to solve.

Basically, I have an 'Entry' worksheet, which new entries are added to. Based off of each entry, a formula checks to see how to categorize each record that is added. Another formula creates a unique identifier so that a summarized view can be created on a different "Journal" tab. There are a couple other formulas that calculate dates and whatnot.

I've found that these formulas are a space hog and simply having them doubles the size of the spreadsheet. Currently, I just have the formulas copied down many rows. Is it possible to not have a formula in these cells until it sees a new record added? I've created dynamic ranges before, but this seems like a slightly different scope since I need the formula to copy down as the range expands.

Any thoughts? I'm stumped, and need to keep these file sizes down to a minimum.

 

[chergh]

Yeah you should be able to use sheetchange event of the workbook to do this. If the new entry is added in column A and the formula is in column B this will fill the column down.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.name = "sheet name" And Target.Column = 1 Then

    Sh.Range(Sh.Range("B2"), Sh.Range("B" & RHLastRow(Sh))).FillDown

End If


End Sub

Public Function RHLastRow(ws As Worksheet) As Long

    RHLastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

End Function

If you had formulas in columns B to E you would change the following line to:

    Sh.Range(Sh.Range("B2"), Sh.Range("E" & RHLastRow(Sh))).FillDown

 

[Rx_]

This is not a universal solution:
Depending on your level of expertiese and your ability to send out code enabled macros.

You might consider writing a custom formula (custom function) in the code section.
The cool thing about that is, it will show up in the insert formula.

For example: I had formulas to average several columns of laboratory samples for drinking water. Although it sounds simple, the automated lab equipment spits out information such as "< 0.5" or "BDL" (below detectable limits". And in one column the "detectable limit" for that equipment was listed.
So, what does an average mean? Well, the specialist gave me the business rules. For some chemicals, BDL was averaged as the limit, for some, as zero, for others it was 1/2 of the BDL. The "< 0.5" had its own rules too.

By creating a custom formula (function), all of the IF, conversions and other situations could be put into one tidy code location. Otherwise, it was huge for each cell.

You should be able to find articles on creating custom functions in Excel.