What formula do I use?

[livinlavidaloca]

I am stuck, trying to work out the formula for this spreadsheet summary and would really appreciate some help!

• I want to create a formula which will leave a SINGLE tick in H3 if there are any non-empty cells in 'HMN.MBY' (the name of the tab I am extracting data from), G3:G34 (the cells within the sheet where I am extracting data from).​
• Unfortunately, the only valid formula I have found so far replicates the column positioning of the non-empty cells from 'HMB.MBY, G3:G34'. It displays the ticks in order of which cells are non-empty in 'HMN.MBY'.​
• What I actually want to do is have a single tick in H3 if there are any non empty cells in G3:G34 from 'HMN.MBY'.​

Hope that makes sense, but feel free to ask if you need more information.

Thank you in advance!!

 

[Phuoc]

Or try:

=IF(OR('HMN.MBY-515.628'!G3:G34<>""),"✓","")

 

[livinlavidaloca]

Or try

=IF(SUMPRODUCT(LEN('HMN.MBY-515.628'!$G$3:$G$34))>0,"✓","")

Woo-hoo, thank you. This seems to work so far! I will carry out some tests and update the forum!

 

[livinlavidaloca]

Fair enough
.. but TEXTJOIN specifying no delimiter - might as well just use CONCAT again?

=IF(CONCAT('HMN.MBY-515.628'!G3:G34)="","","✓")

This also works! Thank you.

 

[livinlavidaloca]

Fair enough
.. but TEXTJOIN specifying no delimiter - might as well just use CONCAT again?

=IF(CONCAT('HMN.MBY-515.628'!G3:G34)="","","✓")

I wish I'd have asked to begin with but would there be a way of changing the CONCAT formula to ignore cells within the range which say 'N/A'?

 

[Peter_SSs]

ignore cells within the range which say 'N/A'?

Can you confirm whether they have a Text value of "N/A" or a formula Error value of #N/A?

 

[Peter_SSs]

Can you confirm whether they have a Text value of "N/A" or a formula Error value of #N/A?

If they are Text N/A values then try

=IF(SUBSTITUTE(CONCAT('HMN.MBY-515.628'!G3:G34),"N/A","")="","","✓")

If they are formula error values try

=IF(CONCAT(IFNA('HMN.MBY-515.628'!G3:G34&"",""))="","","✓")