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
-
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)
Answers
-
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)
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!