Fill across not working as expected?

JLC

Wondering if anybody else is experiencing this - I have performed this scenario hundreds of times without issue so wondering what's up! I have a formula based on full Column:Column ranges. The criteria ranges are set with $, the range to sum is not. When I click the + symbol at the bottom right of the cell and drag it to the right I would expect the columns not using $ to update. However, the formula is actually just copying itself to the right across all applicable cells. Any idea what could be up?

IE I need:

=SUMIFS([2020 Jan 6th]:[2020 Jan 6th], $[Resource name]:$[Resource name], $[Resource name]@row, $[Exclude from summary]:$[Exclude from summary], "")

To become:

=SUMIFS([2020 Jan 13th]:[2020 Jan 13th], $[Resource name]:$[Resource name], $[Resource name]@row, $[Exclude from summary]:$[Exclude from summary], "")

But using the fill across function, all the cells have the first (Jan 6th) formula copied across.

The only thing that I'm doing differently than normal is placing these formulas in the column to the right of the range they're referencing (to avoid circular references). So, the [2020 Jan 6th]:[2020 Jan 6th] formula is being housed in the "2020 Jan 13th" column, the [2020 Jan 13th]:[2020 Jan 13th] column is being housed in the Jan 20th column, etc.

Alejandra

Hi @Jaye Tatone,

I also experienced this when referencing an entire column, however, when I create a single cell reference and drag the formula over to the right, the reference changes to the adjacent cell.

I'm working with our Support team to know whether or not this is functioning as expected and I will let you know what I find.

JLC

Thanks @Alejandra, appreciate any insight you can gather! I'm not sure now if I've done this on an entire column ref before or if it was only for single cell (or ranged cell) refs.

Paul Newcome

I have noticed this too. I remember at one point it was working for me, but I can't remember if it was a whole column reference or a range (where I specified row numbers as well) that updated automatically.

I also remember some time back (maybe a couple of months ago I think) noticing this happening and thinking to myself that it was odd and frustrating because I thought I had remembered doing it before. At that point I was too swamped to look into it then forgot about it until I read this post. I too am interested in what the Support Team says.

Alejandra

Hi @Jaye Tatone,

It looks like this is expected behavior. Our help center article indicates that when you use drag-fill with a formula, the cell references will adjust automatically to reflect the new placement —you're working with a "column reference".  

I also tested this on a previous version of Smartsheet (through our test environment) and the behavior is the same. When you have a moment, please submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet.

JLC

Thanks @Alejandra, I've submitted the product enhancement request now. Appreciate you digging in to this!