Named Range?

[Puppies72]

Hi all,

I have a series of named ranges in a sheet called 'Ships' e.g. 'Ian' should be Ships!A506:A1005, these are used in indirect data validation, however if I sort the sheet Ships! then I lose the named range as it now refers to the same cells but with different content - is there any way I can have a sort of dynamic range name that will always refer to the same cells irrespective of where they are in a sorted list???

as always help appreciated

 

[mikerickson]

Try
Name: myRange
RefersTo: =INDEX(Ships!$A:$A, 506, 1):INDEX(Ships!$A:$A, 1005, 1)

 

[Puppies72]

Hmm doesn't seem to be working for the indirect data validation

In sheet Combat!B11 I have a data validated cell for a list of players - Craig Ian Ricky etc - so I have a drop down list from which I can select a player. I then need C11 to be an indirect dropdown from B11 - using the index formula above to define the named range doesn't seem to work - I have checked formula manager and the name Ian is clearly defined as per your suggestion but the data validation in C11 doesn't give me any options

any ideas??

 

[mikerickson]

If you have three named rangest, Ian, John and Dave, perhaps you could use something like

Name: finalResultRange
RefersTo: =CHOOSE(MATCH(Sheet2!$B11,{"Ian","John","Dave"},0),Ian,John,Dave)