Hi there

Tried to set up a dynamic range name, to be used with data validation.

I used the formula =OFFSET($B1,0,0,COUNTA($B:$B),1)
Created a range name called Therapy, with this formula in the Refers to box.

If I press <Ctrl<G, and type in Therapy, it blocks the Therapy range as
expected, even expanding the range when new items are added. There are no
empty cells in this range.

However, when I set up a data validation cell, with the formula =INDIRECT(H12)
- with H12 containing the word Therapy - I get a message that it evaluates
to an error. Also, nothing happens when I click on the down arrow.

When trying to trace the error, I found that in formula auditing it shows a
#NUM error. I then copied this offset formula into a cell. It behaves as
expected, but has an error flag. When I click the error flag, it shows that
a cell in Col B is empty. When I click on the Trace empty cell option, it
points to cell B1, which contains the word Therapy.

Adjusting the formula to =OFFSET($B2,0,0,COUNTA($B:$B),1), results in B2
being shown as empty, while it also is not.

Any suggestions?
--
HTH

Kassie

Replace xxx with hotmail