Planko Status Column - is this possible?

John Walden

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!

Danielle O'Connell

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.