IF(CONTAINS Formula - if adjacent cell contains text, reference cell in another sheet, else blank

Nick F

I have a sheet where I want to list the client name from a separate sheet in column 1, if column 2@row contains text. Otherwise, leave blank. Currently I have it written as:

=IF(CONTAINS("*",Activity@row){Other sheet range 1}," "))

where Other Sheet Range 1 is the specific cell with the client name. It's not functioning, but also not unparseable, merely giving me a blank return when it should be populating client name from the other sheet. See screenshot:

Thanks in advance for any help!

Nick F

Believe I've solved my question by using:

=IF(ISBLANK(Activity@row),0,{Other Sheet Range 1})

Nick F

Believe I've solved my question by using:

=IF(ISBLANK(Activity@row),0,{Other Sheet Range 1})

Genevieve P.

Hi @Nick F

I'm glad you sorted it out! 🙂

Depending on your sheet set-up, another option would be to use an INDEX(MATCH combination formula so that you can reference the entire column and have it bring back the value dynamically, based on the "Activity" that matches across sheets. This would mean you don't have to create an individual range per-cell that you need brought back.

Here's more information:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

Cheers,

Genevieve

Nick F

@Genevieve P. thanks, I tried using INDEX at first but since this formula is being used in a Control Center blueprint, I didn't have a need for the dynamic returns since it will only apply to a single workspace per deployment, where the range reference won't change within the workspace.

The idea is each workspace created via this blueprint will have individual sheets like the one I shared via screenshot, all listing a single client if Activity is listed. Then creating a Management report that fetches from all these sheets - that's where the INDEX will come in handy...

Thanks for your help!