Populate Q1, Q2, Q3 etc as per Start date and End Date

anilkrag1984

Hi Team,

I have below 2 requirements -

1) Populate Q1, Q2 Q3 etc as per Start date and End Date, I am using the below formula but it is giving error

=IF(AND([Quarter Start Date]@row >= DATE(2020, 8, 1), [Quarter Start Date]@row <= DATE(2020, 10, 31), "Q1'21", IF(AND([Quarter Start Date]@row >= DATE(2020, 11, 1), [Quarter Start Date]@row <= DATE(2021, 1, 31), "Q2'21"))))

The error I am facing is Incorrect Argument.

Attached the screenshot for your reference.

Capture.PNG

2) Once this is resolved another requirement is, In the Smartsheet B I want to pull the Qtr if the Created date falls between the Start date and end in Smartsheet A.

Please note Created date is the system generated Column.

Can you please support to resolve these 2 queries.

Thanks

Anil Raghav

Paul Newcome

You just have a couple of closing parenthesis out of place in your formula. You didn't close out your AND functions is all.

=IF(AND([Quarter Start Date]@row >= DATE(2020, 8, 1), [Quarter Start Date]@row <= DATE(2020, 10, 31)), "Q1'21", IF(AND([Quarter Start Date]@row >= DATE(2020, 11, 1), [Quarter Start Date]@row <= DATE(2021, 1, 31)), "Q2'21"))

For the second part, are you able to provide a screenshot with some mocked up/manually entered data that shows what you are trying to accomplish?

anilkrag1984

Hi Paul Thanks I have resolved both the queries but if you help me with the other one.

I have 2 Smartsheets as Smartsheet A and B

In Smartsheet A I want to populate RGY in such way that if Value in Smartsheet B corresponding Theatre and Qtr as below

For ex if ANZ Q1 value in Customer Training Column in Smartsheet B is 8% then in Smartsheet A it should reflect Red

same as below

• 0-33% Red
• 33-66% Amber
• 67 – 100% Green

Paul Newcome

I'm not sure I follow. Can you provide a screenshot with manually entered data to show what you are trying to accomplish?

anilkrag1984

Smartsheet A.PNG

Smartsheet B.PNG

In Smartsheet A I need to populate RGY in Column Intersight Activation based on the data in the other Smartsheet B. Same columns in both the sheet. Reference to be used as Theatre and Quarter

Paul Newcome

We would start by using an INDEX/COLLECT to pull the appropriate percentage.

=INDEX(COLLECT({Sheet B Percentage Column}, {Sheet B Thatre Column}, Theatre@row, {Sheet B Quarter Column}, Quarter@row), 1)

Then we can use that in a nested IF to say that if it is less than 33%, "Red". If less than 66%, "Amber". Otherwise, "Green".

=IF(INDEX(COLLECT(..........)<= .33, "Red", IF(INDEX(COLLECT(..........)<= .66, "Amber", "Green"))