Validation using a mulit-row/column range

[Belder]

Hello,

I am being lazy, and want to know if this is possible!

I have a range called 'Data', say 5 Row by 2 Col:

Col1 ------ Col 2
Item 1 ------ 1
Item 2 ------ 2
Item 3 ------ 3
Item 4 ------ 4
Item 5 ------ 5


In my validation cell, I just want {Item 1, Item 2,...., Item 5}, but I am too lazy (or efficient?) to have a second named range. My question is: can I use the two column range 'Data' in the validation reference? It looks like you can have a formula in the source box, or use custom and set a formula.

Any ideas? Thanks for your help.

 

[Andrew Poulsom]

Try as Source:

=INDEX(Data,0,1)

 

[Norie]

You could try an OFFSET formula.

This worked for me.

=OFFSET(Data,0,0,ROWS(Data),1)

 

[Belder]

Great! Both solutions work fine.

Thanks for your help. Saves me lots of time and probably halves the amount of my named ranges!