K
Kassie
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
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