Index/Match situation.

megmeyer

That works, and shows 01496 (in two different columns.

Paul Newcome

And it errors when you do this:

=MATCH(INDEX(ANCESTORS([Store #]@row), 1), {231116 IT Install Row 1}, 0)

Have you made sure the cross sheet reference is going across that top row that has the "Store Number For Lookups"?

megmeyer

So, I removed my references, and restarted from scratch. Created the references as I went (so no #INVALID REF).

Used this:

=INDEX({231116 IT Install Checklist Sheet}, MATCH([Store #]@row, {231116 IT Install Checklist Tasks}, 0), MATCH(INDEX(ANCESTORS([Store #]@row), 1), {231116 IT Install Checklist Row 1}, 0))

And I'm back to #NO MATCH.

This is my 231116 IT Install Checklist: (The 2023 New Stores Checklist has not changed (other than the #NO MATCH for the 01 - Validate Server Sys Dat and hwclock cell for Status)

megmeyer

I am the dummy here! I removed my reference row!!!! DOH!

It is now working!!!! Thank you so much for putting up with my inability to follow this.

Sincerely,

Meg

Paul Newcome

Haha. No worries. Glad you got it sorted. Happy to help. 👍

megmeyer

@Paul Newcome - Sorry to bother you, but you seem to know a lot. For unkown reasons, my Conditional Formatting broke when I applied these furmulas to the cells. Instead of the In Progress being Purple, it is green, the section Stuts says it is In Progress, but it is also Green. (it is pulling the In Progress and Complete from the IT Install Checklist - so that's good!)

Any ideas what might cause that?

Paul Newcome

Are you able to provide screenshots of the Conditional Formatting rules?

megmeyer

Certainly! I did try to change the all caps, but it won't let me, and it is working on all the other cells the way it is.

megmeyer

And I have no idea why, but this morning, it is working as expected (conditional formatting, and everything)!

Thank you again for all your help!!

Paul Newcome

Happy to help. 👍️