Option to use different named ranges in formulae

Hi,

I have a file with many look ups using named ranges. I would like to be able to look up from different ranges without having to change the formula.

e.g. in the formula below TableX, Employees and Months are all names of ranges and repeated many times in the file. I would like to be able to, say, allow a user of the file to look up TableY instead but be able to switch back to TableX at any time. Is there an easy way to this without having to have an IF statement used in very cell?

I thought about having the table name to use entered in 1 cell somewhere and then have the formulae look at this to determine which table e.g. cell A1 could have "TableX" or "TableY" entered. I couldn't get this to work with the formula even using &A1 to try to pick up the name.

Does anybody have any ideas on how to do this?

=IF(AND(RIGHT(AW3,1)="0",LEN(AW3)=2),0,INDEX(TableX,MATCH($D3,Employees,0),MATCH(AW3,Months,0)))

Thanks Tony. This does the trick and should be easy to build in throughout the file.

Many thanks