Excel - Indirect with locally scoped named reference

Question

This works:

1. Sheet1: Summary
2. Sheet2: VMware Servers
3. Sheet1 > Cell A1 Value "VMware Servers" - Same as Sheet2's Tab Name
4. Sheet2 > Cell K45 Value "Dell"

Sheet1 > Cell B1 Value "Dell" via Formula =INDIRECT("'"&$A1&"'!K45")

This does not work and I'm stuck:

1. Same
2. Same
3. Same
4. Sheet2 > Cell K45 Value "Dell" - Locally Scoped Name "vendor"

Sheet1 > Cell B1 Value "ERROR" via Formula =INDIRECT("'"&$A1&"'!vendor")

Any ideas?

Answer 1

The issue is with a macro I'm using, I'll open another question for that...sorry