How to reference a single cell in another sheet as part of a formula
Hello all,
I have a sheet that users enter data into monthly, but that data contains all the previous months data as well, so I use a different sheet to pull out that data as part of a formula. In excel that formula is ='Input SO'!B5-sum(B3:B5). This allows me to subtract each previous months data.
The issue I'm having is if I use cell link to link to the cell in another sheet, I can't add anything else to it in the form of a formula. To get around it I just make a reference using the sum function (=SUM({Input SO Range 3}) - SUM([300]2:[300]4)), but I was wondering if there's a better way I'm just not seeing.
Also does Smartsheet have any ability to quickly make these references, or do I have to create each one? For example in excel I can just drag the format and it will change the input it's taking, but I can't seem to get Smartsheet to do the same.
Best Answer
-
Part 1 - Not being able to add anything to the formula if you use cell link
You are correct. Instead of using a cell link the other sheet you can create a cross sheet formula.
Part 2 - Creating the references quickly
You can't set up cross sheet references in smartsheet as easily as you can in excel. But there could be a workaround. Rather than referencing each cell that you need, you could set up one reference for the entire column and edit your formula to use INDEX MATCH or VLOOKUP to find the correct cell within that column. Then you can just drag that formula down.
Answers
-
Part 1 - Not being able to add anything to the formula if you use cell link
You are correct. Instead of using a cell link the other sheet you can create a cross sheet formula.
Part 2 - Creating the references quickly
You can't set up cross sheet references in smartsheet as easily as you can in excel. But there could be a workaround. Rather than referencing each cell that you need, you could set up one reference for the entire column and edit your formula to use INDEX MATCH or VLOOKUP to find the correct cell within that column. Then you can just drag that formula down.
-
I was using a cross sheet formula, just an inefficient one. Thanks to your comment I just changed it to =INDEX{Range},'row#' and that solved my issue and cuts the amount of references I need to make way down. I was overthinking it a bit.
Thank you for your help, saved me a bunch of time.
-
Wonderful! 😍
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!