I have a workbook that has a defined field called tot_sell that resides in cell G:104 and represents the sum of all the numerical entries in the range G:12 thru G:103. (it disregards text or errors)A change in buisness pattern now requires an occasional addition of a few lines which occasionaly gives me an incorrect tot_sell, because of addition or subtraction of lines. (Which shifts the location of Tot_sell)I would like a formula I can add into a seperate cell that totals all numerical lines begining at G:12 and ending one cell up from tot_sell, regardless of tot_sell's position, so I may compare the two cells aat save and warn of discrepancies.Is this possible? Any feedback would be much appreciated.
Totaling based on a defined range that may change location



Re: Totaling based on a defined range that may change location
When you say change location what do you mean? Are you saying that to total in G104 can end up in G100 or G106 based on row additions and deletions?

Re: Totaling based on a defined range that may change location
YesPeople are inserting lines and occasionaly deleting blank lines (rather than hiding them as I would prefer)Its a new development, so I am hoping to use a formula in a different cell to use as a check sum, to compare Tot_Sell with the new formula to quickly flag any changes.Do you follow?

Re: Totaling based on a defined range that may change location
Here you go!!!! =SUM(INDIRECT(CONCATENATE("G12:",ADDRESS(ROW()1,COLUMN()))))

Re: Totaling based on a defined range that may change location
I think I follow but maybe not. If cell G104 is the sum of G12:G103 then deleting a row or adding a row within this range will cause the summed range to expand or contract. Sum also ignores all text entries but will fail if there is an error in the sum range. So I guess my Q is why not use the sum function?


Re: Totaling based on a defined range that may change location
BryceI have some Excel users (pretty good) that mess with the formulas, and we have had MAJOR mistakes, so I want a check sum that will be ALL the entries from g:12, to wherever the Tot_Sum value finaly landsSomeone added a line that did NOT end up in the total (that I call tot_sell).Not sure how, but I have to CYA so I want to compare the two at the save point.

Re: Totaling based on a defined range that may change location
Bill, Did you try my formula?

Re: Totaling based on a defined range that may change location
I get a divide by zero error?

Re: Totaling based on a defined range that may change location
Could you post an example workbook with private data replaced with dummy values?
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!