Auto number based on a criteria
Hello,
Looking for some help with a formula for the following scenario.
I want to assign a Crate # to orders received from a form. When the entry is received, a user will assign a Warehouse to the order. If the Warehouse is Streetsboro, OH, the number should be 1-12, and if the warehouse is N Hollywood, CA, the number should be 13-23.
Best Answer
-
I would start with inserting an Auto-Number column (called "Auto" in this example) with no special formatting.
Then I would use this assuming there will never be more than 12 in the sheet for Streetsboro, OH:
=COUNTIFS(Auto:Auto, @cell < = Auto@row, Warehouse:Warehouse, @cell = Warehouse@row) + IF(Warehouse@row = "N Hollywood, CA", 12, 0)
In the above, be sure to remove the space from between the < and the =. I am not sure what's going on there.
Answers
-
Are you able to provide some mocked up screenshots for context?
-
Hi Paul,
As orders come in, this is generally what it would look like. This would let me link back to another sheet with costs for that crate.
-
I would start with inserting an Auto-Number column (called "Auto" in this example) with no special formatting.
Then I would use this assuming there will never be more than 12 in the sheet for Streetsboro, OH:
=COUNTIFS(Auto:Auto, @cell < = Auto@row, Warehouse:Warehouse, @cell = Warehouse@row) + IF(Warehouse@row = "N Hollywood, CA", 12, 0)
In the above, be sure to remove the space from between the < and the =. I am not sure what's going on there.
-
That works perfectly. I was going down that route, but couldn't quite get the formula right. Thanks for the help!
