Nested If Formulas with cell references that change when the cells they refer to are edited

[CrazyDave]

Good day Excel gurus,

A while back I posted a problem, which was solved, and still works awesomely.
However, I want to put this solution to other formulas and I'm having problems.

This is the original thread.

Cell References changing despite $ being used in the formulas.

That's a pretty simple formula that I started with...
I have other formulas which are nested If Formulas and the cell references change when I edit the data in those cells.... Is there any way to lock the cell references down as in the original solution?
And this is where it gets a little more complicated - I can't share the document, as it contains private and sensitive data...

This is the formula that I'd like fixed, if at all possible...

IF(COUNTIF('WeeklyTimings 2022'!$A$4:$A$33,$A3)=1,IF(INDEX('WeeklyTimings 2022'!$A$4:$B$33,MATCH($A3,'WeeklyTimings 2022'!$A$4:$A$33,0),2)="","a",INDEX('WeeklyTimings 2022'!$A$4:$B$33,MATCH($A3,'WeeklyTimings 2022'!$A$4:$A$33,0),2)),"")

If I change data in the cells referenced in this formula - WeeklyTimings 2022 A4 to B33 - the cells referenced in these formulas change... the A4 becomes A5 or A6, the B33 becomes B25, the A33 becomes A28.... etc. All dependent on what changes I've made... And it is quite frustrating needing to check the formulas on a regular basis...

The formula looks at the cell A3 and then checks the other page - WeeklyTimings 2022 - to see if the name in Cell A3 is in A4 to A33, and if it is, it returns the data in B4 to B33.
Sometimes the name in A3 isn't there, and so nothing is returned. Sometimes the name is there, but the cell in column B is empty, and 'a' is returned.

Many thanks in advance... and hope...

 

[rlv01]

What if you were to use named ranges instead of cell addresses?

IF(COUNTIF(GroupA,$A3)=1,IF(INDEX(GroupB,MATCH($A3,GroupA,0),2)="","a",INDEX(GroupB,MATCH($A3,GroupA,0),2)),"")

Where:

Name	RefersTo
GroupA	'WeeklyTimings 2022'!$A$4:$A$33
GroupB	'WeeklyTimings 2022'!$A$4:$B$33

 

[CrazyDave]

Hello riv01,
Apologies for the delay in replying. And many thanks for your response.
Named ranges is an interesting idea... can a named range be part of a table? The table is A3:B33

 

[rlv01]

I think so. All tables are assigned a name when created and there is nothing to prevent you from defining a new named range that is part of a table. But you would need to experiment.

 

[CrazyDave]

I think so. All tables are assigned a name when created and there is nothing to prevent you from defining a new named range that is part of a table. But you would need to experiment.

Thankyou riv01! I've assigned the range names, and changed four formulas across two sheets. They are working for now...