Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Formula Will Not Work With New Record Added

Posted by: Ray May 23 2020, 05:31 AM

I have below formula running correctly. However, when I add new records under row 28. The formula will not work until I update the formula to include the new records. How can I modify the formula to work automatically with new records added? Thanks!


Posted by: Jeff B. May 23 2020, 07:52 AM

If you want to be able to 'add rows' beneath a range you have already determined, you could:

* add the row(s) "inside" the current range instead of after the bottom of the range
* make your range large enough to encompass any added row(s) (e.g., instead of A2:A28, how 'bout A2:A200?)

Posted by: Ray May 23 2020, 09:27 AM

Thanks for your suggestion. It would work but brings another issue. I have some calculated results on the bottom of record rows. Adding a few rows would not affect the printed report but adding 100 rows will affect the printed results with blank in the central rows. If no other way, probably I have to move the calculated results from the bottom to the top.

Posted by: tina t May 23 2020, 09:54 AM

hello, Ray, i wonder if either of the following links will be helpful:

i googled "Excel flexible range or table", and those are just two of the hits i got. good luck with it. :)


Posted by: Ray May 24 2020, 03:43 AM

Hi Tina, many thanks for your info abt dynamic named range. It is useful for this application!

Posted by: tina t May 24 2020, 10:12 AM

oh, good, hon, glad it's useful. :) i did some stuff in Excel about six years ago, and though i got back to Access as fast as i could, a few things stuck in my mind, but not details. tina

Posted by: ADezii May 24 2020, 11:20 AM

There is another, rather rogue solution, and that is to pro-grammatically create the Formula in the SelectionChange() Event of your Worksheet. This requires no modification of the existing Formula in extending the Rows reference, is dynamic, and no insertion of Rows within the existing Range. Let's assume that you want this Formula in Range("O11") of your Worksheet and you want the capability of adding X number of Rows without manually adjusting the Formula or anything else for that matter. The Code is posted below, and it appears to work very well in limited trials:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lngLastRowWithData

lngLastRowWithData = Range("A" & Rows.Count).End(xlUp).Row

Range("O11").Formula = "=SUMIFS(I2:I" & CStr(lngLastRowWithData) & ",A2:A" & CStr(lngLastRowWithData) & "," & _
                         Chr$(34) & ">=" & Chr$(34) & "&G35,A2:A" & CStr(lngLastRowWithData) & "," & _
                         Chr$(34) & "<=" & Chr$(34) & "&H35)"
End Sub

P.S. - Back again, just figured why not Upload the Demo, it uses your original Formula for reference. Add Data to Rows/Columns in Light Blue Background to see effect. ( 15.52K ): 2