How to reference a single cell in another sheet as part of a formula

Cidniz

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.

KPH

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.

KPH

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.

Cidniz

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.

KPH

Wonderful! 😍