Defined Name Error

[Logo]

Hello All,

I'm going to try and clearly explain my problem and the methodology I'm using. However, bear with me if there is any ambiguity.

I'm using excel 2007 and my goal is to vlookup salary data from a named range on another worksheet using job titles. I have defined the name as such:
"=OFFSET('SALARY DATA'!$A$3,0,0,COUNTA('SALARY DATA'!$A$3:$A$5000),4)"

I have three similar tables representing different classifications of employees and I need to vlookup the from the correct table. Therefore, my vlookup has some additional logic:
"=VLOOKUP(H2,IF(F2="EXPAT",INDIRECT(F2&"s"),INDIRECT(F2&LEFT(G2,1))),2,FALSE)"

The vlookup evaluates to a #REF error. However, if I hardcode the named range i.e. 'SALARYDATA'!$A$3:$D$200 the formula works correctly. Why does the INDIRECT portion of the formula fail when I create a dynamically named range?

Any pointers or possible solutions would greatly be appreciated.

Thanks,

 

[T. Valko]

Hello All,

I'm going to try and clearly explain my problem and the methodology I'm using. However, bear with me if there is any ambiguity.

I'm using excel 2007 and my goal is to vlookup salary data from a named range on another worksheet using job titles. I have defined the name as such:
"=OFFSET('SALARY DATA'!$A$3,0,0,COUNTA('SALARY DATA'!$A$3:$A$5000),4)"

I have three similar tables representing different classifications of employees and I need to vlookup the from the correct table. Therefore, my vlookup has some additional logic:
"=VLOOKUP(H2,IF(F2="EXPAT",INDIRECT(F2&"s"),INDIRECT(F2&LEFT(G2,1))),2,FALSE)"

The vlookup evaluates to a #REF error. However, if I hardcode the named range i.e. 'SALARYDATA'!$A$3:$D$200 the formula works correctly. Why does the INDIRECT portion of the formula fail when I create a dynamically named range?

Any pointers or possible solutions would greatly be appreciated.

Thanks,

The problem is that INDIRECT won't work if the named ranges are defined using functions like OFFSET.

What are the names of the ranges in question?

What are the possible entries for cell F2?

What's in cell G2 and what are the possible entries in cell G2?

You should be able to make this work using a combination of MATCH and CHOOSE but we need to narrow down all the possible range names and when to use a particular named range.