Possible to reference the cell above to increment a # up

gholmes

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!

Paul Newcome

To account for the possibility of more than one transaction for a person on the same day, I would suggest inserting an auto-number column with no special formatting. Then you would use a COUNTIFS like so:

=COUNTIFS([Initials + Month]:[Initials + Month], @cell = [Initials + Month]@row, Date:Date, @cell<= Date@row, AutoAuto, @cell<= Auto@row)

Paul Newcome

To account for the possibility of more than one transaction for a person on the same day, I would suggest inserting an auto-number column with no special formatting. Then you would use a COUNTIFS like so:

=COUNTIFS([Initials + Month]:[Initials + Month], @cell = [Initials + Month]@row, Date:Date, @cell<= Date@row, AutoAuto, @cell<= Auto@row)

gholmes

Thank you Paul - this did the trick! (I misclicked "no this didn't answer my question" instead of yes, but can't seem to undo that, sorry!) This is great - we can pull the import directly out of Smartsheet now - you're the best!

Paul Newcome

Happy to help. 👍️