Reference Another Sheet Ranges Shifting

lmckaig96366

I have a metrics sheet built out referencing a master sheet. The master sheet is a running tracker and each month we add new rows to it. Any time we do this, it shifts my reference ranges on my metrics sheet. We've tried adding new rows to the very bottom of the master sheet as well as inserting the new rows between existing rows. Either way, the ranges get shifted so I have to go in and redefine them (13 in all so it takes some time). Is there a way we can add new rows to the master sheet without messing up the metrics? One interesting thing to note is that the shift isn't always the same. Adding new rows shifts the ranges down but sometimes the metric formulas will read #INCORRECT ARGUMENT SET while other times the formulas still work but they aren't referenced properly so the numbers reported aren't correct.

Part 2. When defining ranges referencing another sheet, is there a quicker way than just dragging the range? The Master sheet we're referencing from is quite large and dragging each reference seems to take forever. I know in Excel you can define a range by typing something like A2:E20. Is there anything in SmartSheet to speed the process up?

Andrée Starå

Hi,

Sounds strange!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Have you tried selecting the whole column? And then limiting what is being pulled by criterion in your formula? For instance, you could also use a helper column to distinguish which rows you want to pull the data from. Then in a sumifs or countifs you could also add the criterion that the helper column has a specific symbol or character or word that requires being pulled into that other report. 

Does that make sense? As Andree suggested, a screenshot will help us see what you're working with and help us give you a more solid answer.

Paul Newcome

Screenshots would definitely help (does anyone hear an echo? hahahaha).

 

Another option would be to pull the metrics on the same sheet as the data and then use cell linking to pull that data to your metrics sheet.

lmckaig96366

Hi Andrée, 

I added a screen shot to my original post, it shows how the referenced range shifts when lines are added to the sheet it is referencing from. 

As far as process, we have a running tracker that is maintained by adding rows (new contracts) a few times each month. The formulas are set up referencing from row two to the bottom of the data but when rows are added to the master tracker, the references on the metrics sheet shift and mess up all of the formulas where I have to go in and update each reference. I can share the sheets with you but we don't have any additions to make at the moment so I'm not sure if it will be much help.

Also, is there a way to be notified when comments are added to a post here? I would have responded much sooner if I realized I had any comments. 

lmckaig96366

Hi Mike, 

I added a screenshot of how the reference shifts when rows are added to the sheet that is being referenced. I can try selecting the whole column, I'm not sure what you mean by a helper column though?

Mike Wilday

I would just click on the column header to select the whole column's worth of data. Then filter out results with criteria in your metric formulas. 

Andrée Starå

Hi Lindsey,

Did you get it working or do you still need help?

Have a fantastic day!

Best,

Andrée