absolute cell reference

[justme]

in column C I have a formula which is the sum of column D to column AA.
=IF(SUM($D5:zz5)<>0,SUM($D5:zz5),"")

Problem: Every day I insert a column next to column C, which becomes the new column D and my formula changes to =IF(SUM($E5:AAA5)<>0,SUM($E5:AAA5),"")

How can I keep the cell reference $D in the formula?

 

[MrKowz]

Try this out:

=IF(SUM(INDEX(5:5,,4):ZZ5)<>0,SUM(INDEX(5:5,,4):ZZ5),"")

 

[justme]

Works, will play a little to figure out. THANKS

 

[MrKowz]

Works, will play a little to figure out. THANKS

To explain a bit how it works, the INDEX formula returns a range reference which can be used anywhere you would normally refer to a range. The goal was to have the first range reference ALWAYS be D5. There were two ways to accomplish this:

1. Use an INDIRECT formula
2. Use an INDEX formula

The INDIRECT formula approach is for a last-resort, as INDIRECT is a volatile function (updates when ANY calculation is performed - very ineffecient). The INDEX function will only update when one of its precedents are updated (the cells actually referenced in the INDEX formula).

So to further explain INDEX, it has the syntax:
INDEX(array, [row number], [column number])

We define the array argument to be 5:5 (aka, the entire row 5). Now, we need to tell the formula what cell we want to return. We can omit the [row number] argument, since this is a 1-dimensional array. Now, we want to tell the formula to return the fourth column (since D is the fourth column), so we put a 4 in for the [column number] argument.

As a result, INDEX(5:5,,4) will always point to D5, no matter how many columns you insert in your data. However, keep in mind that if you add rows above that formula, the range it references will be adjusted. So if you were to add a row above it, the formula will change itself to INDEX(6:6,,4).

Hope that explanation helps! Glad it works for you.

 

[justme]

thanks! Your explanation is good. Now I know how to use for columns!

 

[justme]

=SUM(INDEX(3:3,,sales!4:AA3))


So, to carry this conversation further, I want to do exactly the same thing, but on the summary page. Excel doesn't seem to like that. I get a #VALUE! error. The formula above is in cell B3 in Sheet1, which is the summary page for the sheets named Sales and Orders.

 

[MrKowz]

The part I have highlighted below is not only incorrect syntax, but also not a valid range reference:
=SUM(INDEX(3:3,,sales!4:AA3))

If you want it to return column D, then that argument needs to be 4:
=SUM(INDEX(3:3,,4))

 

[justme]

Thanks for your reply, but how do I get it to know I want the information from the sales page, not the summary page?

 

[MrKowz]

Thanks for your reply, but how do I get it to know I want the information from the sales page, not the summary page?

What do you mean? Can you please elaborate on exactly what you want this formula to do?

 

[justme]

I want the formula to give me the sum of row 3, from column D forward on the sheet labeled Sales. The formula will reside in in the sheet now called Sheet1.