Combining Information from Two Cells to Create an ID Number

Rachael Stammers

Hi I used @John Kennedy solution I found in another post successfully create a unique corrective action ID number which joined data from two different cells (Hazard Report ID # and CA1). I did this by using the formula below:

• =[Hazard Report ID #]@row + "-" + [CA1]

Where

• Hazard Report ID # (HSHR-23-001) is an auto generated number in a separate column
• CA1 is (a hidden column) part of a cell change workflow, so when a new row is added, that cell automatically fills with CA1, CA2 etc

However, I want the Corrective Action Id # cell to remain blank if no corrective action has been identified.

I have tried a number of different formula, without success. The following one does leave the cell blank if there is not Corrective Action identified, however, it shows as incomplete argument for those that do have a Corrective Action assigned

=IF(ISBLANK([Corrective action recommendation 1]@row), "", IF(NOT(ISBLANK([Corrective action recommendation 1]@row, =[Hazard Report ID #]@row, +"-" + [CA1]@row))))

AND

=IF(ISBLANK([Corrective action recommendation 1]@row), "", IF(NOT(ISBLANK([Corrective action recommendation 1]@row, [Hazard Report ID #]@row, +"-" + [CA1]@row))))

All help greatly appreciated

Rachael

Paul Newcome

Happy to help. 👍️

Darren Mullen

@Rachael Stammers you have amis placed parenthesis in your not(is blank()) formula and rush signs that shouldn't be there. Actually that second if statement isnt needed since if isblank(corrective action) is false then the if statement will evaluate the second or false argument.

This should work?

=IF(ISBLANK([Corrective action recommendation 1]@row), "", [Hazard Report ID #]@row, +"-" + [CA1]@row)

Rachael Stammers

Hi @Darren Mullen

Thank you for your help. However, I'm still getting the 'incorrect argument' notice. This is the formula I used

=IF(ISBLANK([Corrective action recommendation 1]@row), "", [Hazard Report ID #]@row, +" - " + [CA1]@row)

Any ideas why...?

Rachael

Paul Newcome

You will need to remove the comma after [Hazard Report ID #]@row.

Darren Mullen

@Paul Newcome good catch@Rachael Stammers Paul caught an error on the formula 😁

Rachael Stammers

It worked! @Darren Mullen @Paul Newcome Thank you both so much. A simple little comma caused me so much anxiety 😉

@Darren Mullen

Paul Newcome

Happy to help. 👍️