Best Of

@Paul Newcome I am getting an incorrect argument set error. If I'm understanding it right, the first data reference is the column I'm pulling from which is the column that 'cars test' is in. The second data point is the column in my raw data spreadsheet, then contains is referencing the exact row that says 'cars test'.

I think you might be able to leverage an Auto-Number column (which would be hidden) that would work with a formula column to produce your result.

So, for example, you have an Auto-Number column, and it just consecutively numbers every row, regardless of transaction type, using whatever set-up you prefer. But, that column stays hidden. (It's shown in the screen cap below for reference, but it does not need to be visible.)

You create a PO Request column using a checklist column type and insert the following column-level formula:

=IF(OR([Payment Type]@row = "Payment Type A", [Payment Type]@row = "Payment Type B", [Payment Type]@row = "Payment Type C"), 1, 0)

("Payment Type A," "Payment Type B," and "Payment Type C" are just placeholders for the payment types that you DO want to produce a PO number - you may have more or less than 3 types - edit as needed.)

Then, in your PO Number column (which WILL be visible), create the following column-level formula:

=IF([PO Request]@row = 1, [Auto Number]@row, "n/a")

(NOTE: you can also just leave the rows that don't need a PO Number blank by replacing "n/a" with "" in your formula.)

Note that the one disadvantage here is that your PO Numbers will have gaps because some will not be used.

Hope this is helpful! Please feel free to tag me in a follow-up post if not, and I'm glad to help. ☺️

So glad to hear it worked for you!

Yes, as long as the cells don't have "Accepted" as the only word, you can have other columns between your ranges as well.

Hey @RaffyM

The number order in your helper column won't matter, as long as you have 1 - 600 (or however many) listed as unique numbers in that column.

If you'd prefer to keep your sheet sorted with 1 - 600 in your Number Helper in order, then what you would do is create a Row Report off of this sheet:

• The Row Report would use this second sheet with the formulas as the Source.
• You would then filter by if the Rank is less than 21.
• Once you have the filtered rows in your Report, you can then sort the Report by the Rank column.

Sheets don't currently auto-sort, but the Report will! Here's a webinar on building a report: SmartStart: Reporting

Happy Friday to you as well 🙂

@Tina Ciak

Make 3 helper columns that simplify your formula:

1. Start Year: =YEAR([Start Date]@row)
2. Start Month =MONTH([Start Date]@row)
3. Start Day =DAY([Start Date]@row)

then your formula for each of the months is the below, replacing the bolded numbers with the corresponding month (e.g. April would be 4)

=DATE(IF([Start Month]@row > 1, [Start Year]@row + 1, [Start Year]@row), 1, [Start Day]@row)