Validation Formula only worked once.

I have a drop down list in cell B40. In the validation I wrote
=If(A40="","",FORTYB) where "FORTYB" is the name of a list. It worked
perfectly, only filling the list if cell 40A had data in it. I then tried to
enter the same format in B41 and it gave me an error something to the effect
of needing a delimited list. I copied the workbook and tried to redo the same
thing I had done in the original A40. Could someone tell me what I have done
wrong? I don't even know that it was supposed to work in the first place!

It will work using the formula you tried *if* instead of manually going
through the process again to set the validation for B41 do it like this:

Select cell B40
Goto EditCopy
Select cell B41
Goto EditPaste SpecialValidationOK

If A41 is empty you won't see any selections in the drop down list in B41.

--
Biff
Microsoft Excel MVP


"Rick Arends" wrote in message
...
I have a drop down list in cell B40. In the validation I wrote
=If(A40="","",FORTYB) where "FORTYB" is the name of a list. It worked
perfectly, only filling the list if cell 40A had data in it. I then tried
to
enter the same format in B41 and it gave me an error something to the
effect
of needing a delimited list. I copied the workbook and tried to redo the
same
thing I had done in the original A40. Could someone tell me what I have
done
wrong? I don't even know that it was supposed to work in the first place!

Think the key sequence that probably happened (when you created the DV in
B40) was that A40 already contained something (it wasn't blank). That's why
you didn't get any error msg. But when you tried to do the same thing, ie
create the DV in B41 via repeating the DataValidation steps, "unfortunately"
A41 was empty, so Excel resolutely threw you the error.

Once you create the 1st DV in B40 (well, you know now how to get it started
- put something into A40), think you could just drag B40 down to propagate
the DVs as desired.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---
"Rick Arends" wrote:
I have a drop down list in cell B40. In the validation I wrote
=If(A40="","",FORTYB) where "FORTYB" is the name of a list. It worked
perfectly, only filling the list if cell 40A had data in it. I then tried to
enter the same format in B41 and it gave me an error something to the effect
of needing a delimited list. I copied the workbook and tried to redo the same
thing I had done in the original A40. Could someone tell me what I have done
wrong? I don't even know that it was supposed to work in the first place!