Formula for Referencing Cell Above Current Cell?

JPFORDTE

I have created this Smartsheet, where my team can record orders raised each month. This would then link to a dashboard (which would have to update automatically each month) to show monthly statistics regarding the orders from that month.

This is what It currently looks like. Each month is child of its year and the orders added in each time are children of that month.

I have create three helper columns: "Ref", "Current Active Month" and "Month" (First Three), which identify what month it is and to have the orders from that active month link to the dashboard.

For "Current Active Month", I have created a formula which checks the boxes for orders from the Current month.

The the "Ref" column assigns a number to activated checked boxes, so I can VLOOKUP them for the Dashboard and would automatically change each month to automatically update the dashboard.

• This is the formula for the first cell of each month for "Ref". It basically says if current month is active then cell@row = 1

• Then each row below that "1"cell basically says, "if parent is not blank, then, cell above + 1".

Basic Process

1. Month is reached, "Current Active Month" checks boxes for orders in that active month.
2. "Ref" column assigns a number automatically to that order for the active month
3. This then allows for VLOOKUP to automatically update the dashboard each month

The issue I am having is that when I add a a new row (e.g when one of team members add in a new order), "Current Active Month" will automatically fill that new row with the same formula, however not for the "Ref" Column.

Here for the new row 15, the formula for "Ref" does not automatically fill, and a need a formula that references the cell above so it can autofill new rows.

Thanks for any help! Very much appreciated.

Paul Newcome

My suggestion would be a few more helper columns with column formulas.

"Auto" (auto-number column with no special formatting).

"Row" (text/number column):

=MATCH(Auto@row, Auto:Auto, 0)

Finally in the "Ref" column your column formula would be:

=COUNTIFS([Current Active Month]:[Current Active Month], @cell = 1, Row:Row, @cell <= Row@row)