Formula Help - IRAAD Profile Data + IRAAD ID

Lori Khoury

Hello Smartsheet Community

Just needing a little more formula help. I have an IRAAD that is being provisioned through a control center blueprint. In this IRAAD I need to add a column formula under the IRAAD Identifier Column. I need to create a unique identifier that takes the Business Partner Alias Profile Data under the detail's column (row 3) and combine it the auto numbered IRAAD ID.

So, the IRAAD identifier column for each entry would have something like "IRAAD0004 - Alias" I need to formula to only create the unique ID if the IRAAD ID @ Row in not blank.

If anyone has any ideas on how to put this together, I would greatly appreciate it.

Thank you as always. This Smartsheet Community group is so incredibly helpful and has saved me so much time trying to figure out. I truly appreciate the help.

Ray Lindstrom

Hi @Lori Khoury,

Try this if you're looking for each row to look at row 3's Details column:

=IF(NOT(ISBLANK([IRAAD ID]@row)), [IRAAD ID]@row + " - " + Details3, "")

Try this if you're look for each row to look at the current row's Details column:

=IF(NOT(ISBLANK([IRAAD ID]@row)), [IRAAD ID]@row + " - " + Details@row, "")

I tested this in a sheet with your column names, and it worked for me. Let me know how it goes for you.

FYI, fellow Optum employee here!

BRgds,

-Ray

Lori Khoury

Hi Ray -Thank you for so much for responding!!!. I tried the 1st formula out because that it what I essentially want it to do. =IF(NOT(ISBLANK([IRAAD ID]@row)), [IRAAD ID]@row + " - " + Details3, "")

However, the one thing that I need it to do is I need to make it a column formula so that I can lock the column. Is that possible since I am referencing a particular cell in a row? I get the error the column formula syntax is not quite right error. If there is a way to tweak the formula so that I can make is a column formula that is what I would prefer.

Otherwise, I was thinking what I can do it use your second formula. however, tweak it by adding a hidden column with a column formula that =details3@row.

Really curious for your feedback :)

Thank you again!!!!

Ray Lindstrom

Hi @Lori Khoury,

I hadn't considered the row reference, and how it would cause an error when converting to a column formula.

You're correct about adding a helper column to insert the Details3 info into. That would solve it! After further testing I found this causes the same error and is not the solution.

I also considered using a Sheet Summary field, but that will cause the same error as using the row number reference.

All the best,

-Ray

Ray Lindstrom

@Lori Khoury,

I've tried this using the helper column you mentioned, and I get the same problem too.

You can try to hide the fact that you're making an absolute reference by using a helper column or Sheet Summary field, but it still recognizes/catches it and results in an error.

A helper column or Sheet Summary field would both work IF they weren't using an absolute reference.

Will the value "Alias" from row 3 ever change? If not, just hard code it into the formula or into a Sheet Summary field as a string of text.

Smartsheet post about this (see Column Formula Limitations section): https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas