Absolute cell reference formula help

johnny5

New Member
Joined
Nov 13, 2003
Messages
7
I have a spreadsheet that calculates count and sales by item for a 12 month rolling period. I've been updating the formula everytime I add or delete a month to create a new 12 month period. I need a formula to sum the same cell references for each item and not be affected by the addition or deletion of columns. I tried the INDIRECT function but I cannot figure out the correct syntax because I am not using a constant range. Any help would be appreciated.
Book1
ABCDEFGHIJKLM
1ItemJan-05Jan-05Feb-05Feb-05Mar-05Mar-05Apr-05Apr-05May-05May-05TTLTTL
2NumCountSalesCountSalesCountSalesCountSalesCountSalesCountSales
3MX-123223$8,920.00193$7,720.00273$10,920.00217$8,680.00225$9,000.00
4TL-500117$4,680.0086$3,440.00115$4,600.00125$5,000.00111$4,440.00
5ES-445441$17,640.00413$16,520.00462$18,480.00380$15,200.00290$11,600.00
6MT-600621$24,840.00561$22,440.00600$24,000.00580$23,200.00582$23,280.00
Sheet1
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
The absolute cell reference won't work?
You probably blew right past me but
I would think that =$C$3 in cell AC3 would equal $8920
until you deleted colums C and D then it
would equal $7720 in cell AA3
probably missed your point
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
Would something like this work?
Book12
ABCDEFGHIJKLM
2PartCountSales
3TL-500554221201174680863440115460012550001114400
Sheet1


In B3 =SUM(INDEX(3:3,,4))+SUM(INDEX(3:3,,6))+SUM(INDEX(3:3,,8))+SUM(INDEX(3:3,,10))+SUM(INDEX(3:3,,12))

In B4 =SUM(INDEX(3:3,,5))+SUM(INDEX(3:3,,7))+SUM(INDEX(3:3,,9))+SUM(INDEX(3:3,,11))+SUM(INDEX(3:3,,13))

Ugly and probably not the best way to do this but...
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
Got it looking a little better here



=SUM(INDEX(3:3,,4),INDEX(3:3,,6),INDEX(3:3,,8),INDEX(3:3,,10),INDEX(3:3,,12))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,193
Members
412,447
Latest member
immy
Top