SUMIF Formula - "Dragging" to Copy

sstikons

For the formula below, when I drag this formula, which references another sheet, across columns in the sheet that I'm writing it, the column referenced in the formula doesn't change. Even though I've done nothing to anchor it, it just remains anchored on the column against which I wrote the original formula, in this case {Total Cash Actuals-Forecast Range 2}. Why is this happening? Also, why instead of inserting cell references (e.g. [Column3]2:[Column3]100) in the other sheet is my formula being populated with a range reference from the sheet?

SUMIF({Total Cash Actuals-Forecast Range 1}, $[Column2]3, {Total Cash Actuals-Forecast Range 2})

Paul Newcome

The Range reference is just how Smartsheet refers to the range of cells selected from other sheets.

 

The reason it is not updating your column reference would be the $ before [Column2]3.

sstikons

Paul, thanks for your reply.

To clarify, I want [Column2]3 anchored, which is why I have the $. It's the Sum_Range (3rd argument) that I'm referencing in the other sheet that I want to vary by column when I drag the formula, not the Range (1st argument).

Paul Newcome

Ah. Ok. I see what you are trying to do now. A Range reference is already (for lack of a better term) "Locked in".

 

I can see how in your case being able to use cell references when referencing another sheet would be useful, but as of right now I am unaware of a way to do that.

 

Unfortunately the only way that I can find to have your sheet display the data you want is to create a different range from Sheet 2 for each cell in Sheet 1 that you want populated.

 

You could always put in an enhancement request with Smartsheet asking for that flexibility and see what they say about it.

 

I hope I was able to answer your question this time around.