Change date based on IF(AND formula

angelapaj

Hi,

I have a problem with my formula and I really hope you can help.

So basically looking at my formula, the “{2B-0.5 Service Parts Order (Oct) Range 1}“ is the part number (reference to the column in different sheet) and it needs to be the same as the part number column in the sheet where the formula is, {2B-0.5 Service Parts Order (Oct) Range 2} is referencing the column in the other sheet - this column is a CSPL Name and it needs to be the same as the CSPL NAME in the sheet where the formula is. If both conditions are met, then smartsheet needs to look at the PO Submission Date column and add a specific number of days to this date - the number of days is provided in the different sheet {2B-0.5 Service Parts Order (Oct) Range 3} - it needs to look for a line with needed part number and CSPL name and then take a number of days from the highlighted column (range 3) and add it to the PO submission date.

My formula looks like that: 

=IF(AND({2B-0.5 Service Parts Order (Oct) Range 1} = [Part Number]@row, {2B-0.5 Service Parts Order (Oct) Range 2} = [CSPL Name]@row), [PO Submission Date]@row + {2B-0.5 Service Parts Order (Oct) Range 3})

It doesn't work as I get the invalid operation error.

Can you advise please?

Paul Newcome

You will first need an INDEX/COLLECT to pull over the number of days based on the match and then add it to the date.

=[PO Submission Date]@row + INDEX(COLLECT({2B-0.5 Service Parts Order (Oct) Range 3}, {2B-0.5 Service Parts Order (Oct) Range 1}, @cell = [Part Number]@row, {2B-0.5 Service Parts Order (Oct) Range 2}, @cell = [CSPL Name]@row), 1)

Paul Newcome

You will first need an INDEX/COLLECT to pull over the number of days based on the match and then add it to the date.

=[PO Submission Date]@row + INDEX(COLLECT({2B-0.5 Service Parts Order (Oct) Range 3}, {2B-0.5 Service Parts Order (Oct) Range 1}, @cell = [Part Number]@row, {2B-0.5 Service Parts Order (Oct) Range 2}, @cell = [CSPL Name]@row), 1)

angelapaj

@Paul Newcome it worked - thank you!

Paul Newcome

Happy to help. 👍️