UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formula Will Not Work With New Record Added, Office 2013    
 
   
Ray
post May 23 2020, 05:31 AM
Post#1



Posts: 577
Joined: 31-January 00



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!

=SUMIFS(I2:I28,A2:A28,">="&G35,A2:A28,"<="&H35)
Go to the top of the page
 
Jeff B.
post May 23 2020, 07:52 AM
Post#2


UtterAccess VIP
Posts: 10,491
Joined: 30-April 10
From: Pacific NorthWet


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?)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Ray
post May 23 2020, 09:27 AM
Post#3



Posts: 577
Joined: 31-January 00



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.
Go to the top of the page
 
tina t
post May 23 2020, 09:54 AM
Post#4



Posts: 6,692
Joined: 11-November 10
From: SoCal, USA


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

https://www.excel-easy.com/examples/dynamic-named-range.html

https://exceljet.net/lessons/how-to-create-...ge-with-a-table

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

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Ray
post May 24 2020, 03:43 AM
Post#5



Posts: 577
Joined: 31-January 00



Hi Tina, many thanks for your info abt dynamic named range. It is useful for this application!
Go to the top of the page
 
tina t
post May 24 2020, 10:12 AM
Post#6



Posts: 6,692
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ADezii
post May 24 2020, 11:20 AM
Post#7



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


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:
CODE
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.
This post has been edited by ADezii: May 24 2020, 11:49 AM
Attached File(s)
Attached File  SumIFs.zip ( 15.52K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 11:13 AM