Planko Status Column - is this possible?
Sheet 1:
Col 1: Numbers in row sequence, i.e. 1111, 1112, 1113, 1114
Col 2: Status: Show / Unavailable
Sheet 2:
Col 1: Last Name
Col 2: Number (index/matched from Sheet 1 based on Status Columns)
Col 3: Status: Show
Sheet 2 is a manual row entry sheet, and what I’m hoping to achieve is that when the status changes in Col 3 to Show, the next available number from Sheet 1, populates into Col 2 on Sheet 2.
I can get the number to auto populate by Index/Matching the Status columns, but having trouble figuring out, then, how to make the number that was just used unavailable for the next entry.
I’m thinking if there is a way to make all the numbers in Sheet 1 “unavailable” except the next available (i.e. “Show”), then once used, it would become “unavailable” and the next number in sequence would changed to “Show” so it could be used when Sheet 2 status changes to “Show”.
Basically…
1111 – Unavailable
1112 – Show
1113 – Unavailable
1114 – Unavailable
Once 1112 is used, then…
1111 – Unavailable
1112 – Unavailable
1113 – Show
1114 – Unavailable
Does this make sense? Or is there a better approach? THANKS!
Answers
-
So my solution only works if you get the number in sheet 2 populates first.
This is the formula you could type in Column2 in Sheet 1:
=IF(MAX({Column2Sheet2}:{Column2Sheet2}) = ([Column1]@row - 1), "Show", "Unavailable")
For {Column2Sheet2}:{Column2Sheet2} you would need to do a reference to that sheet. Basically, for whatever cell your in for status in sheet 1, if that number doesn't appear already in the sheet and the greatest number in the sheet is 1 less than that number it will put the status as show and otherwise have it as unavailable.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!