Sequential Numbers "IF"

Al W

I have a project management template that I am trying to set up with a column to produce sequential purchase order numbers only if a column named "ORDER DATE" is populated in specific category rows I have named "ORDER MATERIALS". Can this be done? I am not too good at formulas, so the more specific, the better for me in writing the formula (assuming it can be done).

(EDIT) I think one of the issues could be that we have many projects, so the sequential numbers may have to be pulled from a separate Sheet.

Smartsheet has been a great help for my job!

 

Al W

Paul Newcome

How will your order numbers be listed? Do you want them sequential by row regardless of when Order Date is populated, or will it be sequential based on the date with the first one populated being the lowest number and the most recent, the highest number?

Al W

The order of the numbers is not important to me really, but ideally the sequential number would take advantage of the prefix such as 18 for the year. Keep in mind there are other rows in the project task list that will not have a purchase order number as they are not material orders. I have the cells grayed out where purchase order numbers are not required. I currently have a hyperlink going to another sheet set up with the sequential purchase order numbers but it is duplicating data entry of the vendor, type of material etc. It would also require manually taking the number from this sheet and re-entering it into the project sheet which could cause a chance of error. 

I really just need a purchase order number to use when ordering the materials to use as a reference when the vendors return paperwork so I can assign/attach it to the right line item. As mentioned in the original request, I would like the sequential number to populate only when the column named Order Date has a date entered in it.

Paul Newcome

Unfortunately you cannot automate the prefix when using an Auto-number type column to establish a Row ID number.

 

The easiest suggestion I can make is to add your auto number column and set up your layout for how you want the number displayed WITHOUT the prefix. This will give you a Row ID for all rows. To establish your automated prefix and only populate the rows where [Order Date] is a date, create a new text/number type column.

 

=IF(ISDATE([Order Date]@row, RIGHT(YEAR([Order Date]@row), 2) + " - " + [Row ID]@row)

 

What this does is if the Order Date column of whatever row the formula is in has a date in it, it will take the right two digits of the year and join it with your Row ID number with a - in the middle. So if your order date is 10/10/2018 and the Row ID is 73, then it will display 18 - 73. To remove the - in the middle, simply delete the BOLD UNDERLINED portion of the formula.

 

You can then hide your Auto-number column and move your column with the formula in it into its place.

 

If you are having trouble figuring it out to tweak this to exactly how you want it displayed then let me know, and I will try to help you out. with a more exact formula for you.

Al W

Paul

I did not succeed with the formula. I have attached a snapshot of my PM page that may help you understand my problem. My other concern with the process you describe is that would the formula you suggest possible create a duplicate PO number in one of the other projects if on the same row number on the same date?

As you can see from the snapshot, there are many rows that do not require a purchase order number. I highlighted the rows where an order date for materials would be entered and on the far right are columns which are for the actual PO reference, one that I added for the formula you suggested plus the original one I had to enter the PO number. Perhaps I am over complicating this. 

The Order Date would go in the Column "Actual Submit / Ord / Start Date" in the "3a. Order Materials Materials row".

Paul Newcome

To avoid having duplicates over multiple projects, you can take advantage of the prefix in the auto-number type column to differentiate between them.

 

Below you will see some screenshots. The first one shows how I set up the auto-number system. Second shows the formula in the first row. Third shows it being used in more rows with one of them not needing a PO number (to show that it won't populate one). The fourth shows the Row ID column hidden. Of course there is a lot of flexibility in how your PO number is determined between the prefix denoting the project, the numbers displayed from the Row ID, the delimiters used, how the year is specified, etc.

 

If something like this works for you, but you want a different format for your PO numbers, let me know, and I can tweak it to fit your needs more specifically.

Al W

Thank you Paul

I will play around with this tomorrow.

 

Al W