Data validation source from sheet 1 to sheet 2 keeps losing sheet 1 ref

[Ironman]

Hi

I'm racking my brains trying to work out why the data validation range keeps losing the sheet reference.

The validation dropdown reference in sheet 'Indoor Bike' should be ='Training Log'!$L$2:$L$7 but every time I re-open the workbook and recheck the cells, it has changed to =$L$2:$L$7 so it won't work, even after I correct it again. If it makes any difference (though I don't see why) I am manually selecting the range, not typing it in.

I'm totally stumped why this is happening (it didn't use to, but I can't identify what caused the problem to start).

Help appreciated, thank you!

 

[jasonb75]

Silly question, are you saving it after correcting it?

Also, I notice in one of your previous threads that you are using vba to set some validation, if anything like that is being applied to the range affected in this thread then that would most likely be the cause.

 

[Ironman]

Hi Jason, sure, but you had to ask of course.

Yes thanks, I did check the validation codes and none of them relate to Column i/9.

I have since copied the range to the same (Indoor Bike) sheet and the validation works fine so it's not the end of the world - I'm just frustrated not understanding why the sheet reference no longer works.

 

[jasonb75]

Have you tried using a named range instead?

 

[Ironman]

Yes, but because each of the 6 cells in the range represents a different value that I select from a dropdown list then I don't think that's possible?

 

[jasonb75]

That doesn't mean that it can't be named. Create a named range that refers to ='Training Log'!$L$2:$L$7 then use the name as the validation source and see if that cures it.

 

[Ironman]

Yes,thanks, I did try that but the drop down just showed the name I'd given to the range, not the individual 6 options to select from.

 

[jasonb75]

Sounds like you forgot to put the = sign in front of the name when you assigned it as the validation source.

 

[Ironman]

Agggh, yes! Thank you Jason!

That worked and was still OK when I reopened the workbook, thank you!