Possible to reference the cell above to increment a # up


Hi folks!

I've got a series of credit card transactions that need importing, so the format needs to be just so...

I've got a column named "Initials + Month" (employee initials + 3 ltr month & yr). What I need to tack onto that is...during a given month, which numbered transaction is it for that individual employee? The first? Second? Fifteenth?

What I'm looking to calculate is the "MonthlyTrans#" column in the example above where I've got 2 facetious employees transactions over 3 months (Nov-Jan).

I accomplished this in Excel by sorting the sheet by staff, then by date, and putting in a formula in the MonthlyTrans# column to compare what was in the "Initials + Month" column with the same "Initials + Month" column in the row above the cell formula, and if so, return the value directly above the formula-cell ("MonthlyTrans#" column) plus 1, or if it didn't match, assign this as #1/the first.

I'm not sure if the "look at the cell above" trick can work in Smartsheet, but is there any way to pull off something like what I'm talking about? Right now I have to pull the whole Smartsheet into an Excel sheet with a couple of helper columns, but I'd like it if this could be pulled off entirely in Smartsheet (if we also made sure to sort it by employee, then by date).

Thank you!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!