Once date is inputted, tick on summary tab goes

[livinlavidaloca]

Hi all, REALLY WOULD APPRECIATE HELP ON THIS ONE

I'm struggling with this one. So I've used Substitute & Concat in a formula to a Summary page to check other tabs for a '!' in any of the cells (which means action required), if there is a '!' it will do a tick on the summary tab.

This has worked well so far, as I have specified that the formula on the summary doesn't tick if there is 'N/A' but I also need the tick on the summary page to disappear when a date is inputted into the tabs (because this means the date it has been resolved.) I don't know how to refer to dates within a formula on Excel, so I tried to simplify it by using a tick in the tab instead of the date it was resolved...

This is what I tried: =IF(SUBSTITUTE(CONCAT('HMN.MBY-515.628'!$G$7:$G$38),"N/A","","✔",""))="","","✔") but THEN it says too many arguments.

Does anyone have an idea how I can change this formula to pick up on cells that have '!" but ignore cells with a "N/A" AND either a "✔" or date?

 

[GlennUK]

You have too many items in your substitute for a start off. If you want to substitute multiple things, then nest your substitutes, like SUBSTITUTE(SUBSTITUTE(whatever,"N/A",""),"✔","")