Reference Named Ranges in a formula based on a cell

[AiriA]

I have two name ranges Flash445 and FlashCal and would like to reference them in a formula. I would like to use the Indirect formula and use a static "Flash" then reference a dynamic cell in A1 that can change from 445 or Cal. I'm sure it's a pretty easy formula but for the life of me cannot get anything to work. Any help would be greatly appreciated.

[Norie]

Try this.

Please Login or Register  to view this content.

[AiriA]

I get the dreaded #REF! message. I should have copied the entire formula but here it is: =INDEX(INDIRECT("Flash" & A1),MATCH($B11&" "&$B$10,Flash445!$B$3:$B$14,0),MATCH(C8,Flash445!$B$2:$R$2,0)). Now if I just type in Flash445 in place of the Indirect formula then it works fine.

[Norie]

What exactly is in A1?

Is Flash445 also a sheet name?

You seem to be using it as one here.

Please Login or Register  to view this content.

[AiriA]

A1 will either have 445 or Cal and "Flash" will be static.

Yes, I also have a sheet name Flash445 (maybe a bit confusing) but I want to reference the Named Ranges Flash445 because it is a dynamic and can grow or shrink depending on the available data. I'm only referencing the sheet name for the column and row headings. But I'm probably going to change those as Indirect also so everything references the correct sheets and named ranges. I hope that makes sense because it's starting to confuse me also.

[Norie]

The problem is having the sheet called Flash445.

INDIRECT thinks you are referring to that rather than the named range.

[AiriA]

I just changed the sheet name from Flash445 to Test so that it doesn't confuse with the named range but I still get the same #REF! result. Any ideas?

[Norie]

Can you attach the workbook?

[AiriA]

The formula that I'm trying to create is in D11. So the jist of it is that I need to be able to toggle between calendar types (445 or Calendar) and have the appropriate data appear. I ws successful with vlookups but was trying to simply the formulas. Please let me know if you have any questions. Thanks again.

[Norie]

I'm sorry but I'm really at a loss here.

The INDIRECT with the named range seems to evaluate to #REF! wherever it's used.

I tried changing the formula to refer to the range that the OFFSET evaluates to, B3:Q5, and that doesn't give an error but it gives the wrong answer.

Oops, no wonder it's wrong I used Calendar instead to Test.

When I change to Test the formula works and the answer is correct.

[AiriA]

Okay, now I'm at a loss. Should there be an Offset formula also?

[Norie]

I was referring to the OFFSET formula you are using to define the named range.

[AiriA]

Is there an explanation why the first formula does not work versus the second one that does? It seems the same.

=INDEX(INDIRECT("Flash" & A1),MATCH($B11&" "&$B$10,Test!$B$3:$B$14,0),MATCH(C8,Test!$B$2:$R$2,0))

=INDEX(Flash445,MATCH($B11&" "&$B$10,Test!$B$3:$B$14,0),MATCH(C8,Test!$B$2:$R$2,0))