Indirect-Index-Match and Circular Reference error

[NikToo]

I have a problem I'm just about to give up on. I've inherited a report from someone who's left the company and there's a bunch of circular reference errors in it (no one bothered checking). I can not for the life of me figure out what's causing it though. The cell above works with the exact same formula but a slightly different lookup value (somethingsomething 503 instead of somethingsomething 771). The cell formula is thus:

=IFERROR(INDEX((INDIRECT("'"&$D$10&"'!$a$15:$zz$1000")),MATCH($A94,(INDIRECT("'"&$D$10&"'!$a$15:$a$1000")),0),MATCH(J$11,(INDIRECT("'"&$D$10&"'!$a$15:$zz$15")),0)),"")

D10 is a text value from a dropdown, J11 basically refers back to D10. A94 is the somethingsomething 771. It basically finds itself and goes and finds a column based on the value in D10. I don't know if it's the format of A94, because as I said if I change it to 503 as the line above it, it works. Change it to anything else and it won't work.

Any help is much appreciated...

 

[steve the fish]

Which cell and sheet is the formula in and what is in D10?

 

[NikToo]

The sheet is called "Major Corporates", the cell is J94, and D10 has a choice of QTDA, YTDA, Budget and pYTD, so just column names in row 15.

 

[steve the fish]

Column names? D10 should be sheet names.

 

[NikToo]

I'll be darned, so it is! It refers to some hidden sheet which was missing the value. **** these people!

Thanks Steve. Much appreciated. I have loads of these errors to fix so it's hugely appreciated even.

Still getting a circular reference error though...