Named Ranges

[Orbit]

So here is what I am trying to do. I have a drop down list that has a bunch of names. The names are Q3_2011, Q4_2011, Q1_2012, Q2_2012, Q3_2012, Q4_2012, Q1_2013, Q2_2013, Q3_2013, Q4_2013, Q1_2014, Q2_2014, Q3_2014, Q4_2014, Q1_2015, Q2_2015, Q3_2015, Q4_2015.

I created two named ranges for each of the above, Ex.: Q3_2011_1 and Q3_2011_2.

These will be used in an index match match formula that I have already completed.

What I want to be able to do is change the drop down to Q3_2011, and have the sections of the formula update to Q3_2011_1 and Q3_2011_2.

I just don't know what to put into the formula to get this to work.

Something like (assuming the drop down is in B4): =B4&"_1" just displays Q3_2011_1 as a sting not as a named range.

Any help is much appreciated.

 

[L. Howard]

Try

=Indirect(B4)&"_1"

Not sure it will work, but... easy to try.

Regards,
Howard

 

[Orbit]

Gives me a #REF! error, it doesn't seem to like that one.

 

[Orbit]

While that didn't work....

=indirect(B4&"_1") seems to work though. I will try it out and see if the whole formula will calculate with me using that method. Thanks for pointing me in the right direction.

 

[Orbit]

Doesn't work in my index match match formula for some reason, but it works on it's own..

 

[L. Howard]

Maybe for the formula just B4&"_1" as a string?

Maybe post the formula, one of the guru's might see something obvious.

Howard

 

[Orbit]

The formula is: =INDEX(INDIRECT(C6&"_1"),MATCH(),MATCH())

The matches don't really matter at this point, unless I can get the first range to work, I am going to have to find a different solution.

 

[Orbit]

I should mention that when it worked, my named range was only one cell. In the formula, the named range is an actual range on another sheet in the workbook.

 

[Orbit]

Any Gurus on today?

 

[Caribeiro77]

Any Gurus on today?

Not a guru,(not even close) but i would like to give it a try....
Can you share the workbook with us?