bearcub
Well-known Member
- Joined
- May 18, 2005
- Messages
- 732
- Office Version
- 365
- 2013
- 2010
- 2007
- Platform
- Windows
Years ago I learned of an excel hack where you could replace the last range reference in a range with a cell refence using the name manager. The sum formula would have to be below the table like this:
The formula, as you can see is in AG 75. I replace the AG 74 will the RA which references the row above the $710,248.89.
Within the help of the name manager and RA range, if I wanted to include new values after row 73 I would need to manually change the cell range from AG 73 to AG74.
Is there a way to create an expanding range for formulas that would be added to the top of table. Meaning, if I inserted a row at the top of the table so the formula was in AG1, the RA formula would not work because I would not be referencing the cell before the formula. I looked at using the Index formula as the second range parameter but that includes a fixed range so it would not excpand.
Could I use the CountA function to replace the cell reference: Sum(Ag2: AG73) to Sum (Ag2: Counta(Ag2:Ag1000))
The formula, as you can see is in AG 75. I replace the AG 74 will the RA which references the row above the $710,248.89.
Within the help of the name manager and RA range, if I wanted to include new values after row 73 I would need to manually change the cell range from AG 73 to AG74.
Is there a way to create an expanding range for formulas that would be added to the top of table. Meaning, if I inserted a row at the top of the table so the formula was in AG1, the RA formula would not work because I would not be referencing the cell before the formula. I looked at using the Index formula as the second range parameter but that includes a fixed range so it would not excpand.
Could I use the CountA function to replace the cell reference: Sum(Ag2: AG73) to Sum (Ag2: Counta(Ag2:Ag1000))