Trying to subtract a value on one sheet from another value on another sheet

olsxn

Hi, I'm not great at formulas and I'm struggling with something that I feel should be rather simple.

I want to compare some month on month data, the current months data is held in one sheet and the last months is held in another. So I used the formula below for the first cell (data range 1 being the specific cell in row 1 of the same column) which worked perfectly as expected.

=SUM([ColumnInCurrentMonthSheet]@row - {LastMonthSheetDataRange1})

I can't then drag down this formula and expect it to work perfectly as the cross sheet reference is referencing 1 specific cell on the other sheet.

If I change this reference to the entire column it throws back #INVALID OPERATION. I tried adding "@row" at the end of the reference but that becomes #INVALID REF.

I assume I need a different formula entirely, something expandable that I can then drag down or maybe turn the column into a formula column?

I have quite a number of columns that I want to compare month on month, with almost a hundred rows, so I can't bring myself to use that first formula and create a new reference in each cell (also a complete waste of Smartsheet's functionality I'm sure).

Any help with this would be greatly appreciated, thanks!

MBBAKER

Hi!

I think that you would need to use an index match to help with this. I am assuming that the sheet has some sort of column that has a unique value that could be matched up to last months sheet. If so it would look something like what I have below. Keep in mind when you set up your cross sheet reference range that you need to select the header of the column for it to look at all values and not just the single cell that you select.

=[ColumnInCurrentMonthSheet]@row- INDEX({LastMonthSheetDateRange value to subtract}, MATCH([ColumnInCurrentMonthSheet unique identifier]@row, {lastmonthsheetdaterange unique identifier},0))

Hope this helps!

Mary Beth

MBBAKER

Hi!

I think that you would need to use an index match to help with this. I am assuming that the sheet has some sort of column that has a unique value that could be matched up to last months sheet. If so it would look something like what I have below. Keep in mind when you set up your cross sheet reference range that you need to select the header of the column for it to look at all values and not just the single cell that you select.

=[ColumnInCurrentMonthSheet]@row- INDEX({LastMonthSheetDateRange value to subtract}, MATCH([ColumnInCurrentMonthSheet unique identifier]@row, {lastmonthsheetdaterange unique identifier},0))

Hope this helps!

Mary Beth

olsxn

@MBBAKER perfect, thank you so much this is a super useful thing to learn!