VLOOKUP using INDIRECT reference to a tab throws #N/A

Hello, All.

I have a multitab workbook, some of the tabs of which are named like pr01cc00 (no need for you to know why), and others are named like DataDict PR. As you can see, the name of the tab begins with the two characters that match the last two characters of the DataDict tab, in this case, PR.

My formula is =VLOOKUP(B2,INDIRECT("'DataDict "&UPPER(LEFT(RIGHT(CELL("filename"),8),2))&"'!$A:$D"),4,FALSE). Here is how to parse it.

1. Cell B2 contains a value like pr01001.
2. UPPER(LEFT(RIGHT(CELL("filename"),8),2))) pulls the first two characters off the tab name and upcases them.
3. The result is prefixed with DataDict to yield DataDict PR, the name of the DataDict tab that contains the item to be found.
4. The INDIRECT creates the reference, to which is appended an absolute reference to columns A thru D on the DataDict tab.
5. The VLOOKUP finds the target.

The formula works. But here is my problem... As I said, there are multiple tabs like pr01cc00 and I have this formula in multiple rows on each tab. The formula works, but on some tabs it throws #N/A on all rows. If I recalc the sheet, the values are found, but then other tabs throw the #N/A on all their rows. If I recalc those sheets, same story with some of the other tabs. If I recalc the workbook, same story. Not all the tabs throw the #N/A; only some do, and it changes around each time I recalc.

STOP THE MADNESS! What is going on?

mikeTRON,

I appreciate the work-around, but I would really like a solution to the problem itself. INDIRECT formulas useful in many respects and understanding what is causing the problem will make the use of the formula more likely in appropriate circumstances.

Hi, Tony.

It means I go to Formulas > Calculate Now.

Tony,

Now that you bring it up, I do get REF errors, but they, too, go away if I recalcualte, though others appear elsewhere (but not in the same tab as the #NA). Weird. Do I need to upload the workbook?