Reference Another Sheet Ranges Shifting
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?
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
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.
-
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?
-
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.
-
Hi Lindsey,
Did you get it working or do you still need help?
Have a fantastic day!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!