Indirect and offset commands doesn't work together

[pruleone]

Hy,

I use under validation command indrect command. It all works fine.

basically i have =indirect(K1)
K1 = If(L1=1;"One";if(L1=2;"Two";if(L1=3;"Three";"")))
One = A3:A7
Two = B3:B6
Three = C3:C9

Now I decided to change this validation littlebit, so I inserted some assistance lines to use offset command.

A1 = count (A3:A10)
B1 = count (B3:B10)
C1 = count (C3:C10)

A2 = N1
B2 = N2
C3 = N3

To use offset menu I add new contents to One, Two and Three.

One = offset(A2;A1;0;-A2;1)
Two = offset (B2;B1;0;-B2;1)
Three = offset (C2;C1;0;-C2;1)

Now if I will insert new data to columns A, B or C I don't need to change selection for One, Two or Three. It would change automatically.

Idea is nice (I think so ), but it doesn't work.
I will get error message from indirect command after doing those changes.

What I have to change so, that it could work?

 

[GlennUK]

Have a look at Debra Dalgleish's site for some ideas,

http://www.contextures.com/xlDataVal15.html

as your approach is not usuable ( you just can't do an indirect on a dynamically defined range as a data validation ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.