2 Data Validation.. but with a List

[John]

Another slightly different Data Validation than my previous post

I have a Drop Down List within DV (from RN "Cost_Types), but I only want to
be able to select from the list if E10 has a value within it.

Have put the following within Custom, but then I don't get the "Drop Down
Choice" - =AND(INDIRECT("E10")<>"",Cost_Types). Also tried it within List
but get List must be delimitted error. What have a incorrect?


Thanks

 

[Bob Phillips]

You might be able to do something with this formula in the List type

=OFFSET(Cost_Types,0,0,(COUNT(Cost_Types)-1)*($E$10<>"")+1,1)

what it does it show the whole list if e10 is not blank, just the first item
if it is.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Bob

Very smart solution!!

If John's cost types are text however, he will need to change COUNT to
COUNTA or maybe better still use COUNTIF(Cost_Types,"<>") to cover either
text or numerics.

=OFFSET(Cost_Types,0,0,(COUNTIF(Cost_Types,"<>")-1)*($E$10<>"")+1,1)

Regards

Roger Govier

 

[Bob Phillips]

Just played with this a bit more, doing something that I assumed would not
work, but surprisingly (to me at least) does.

A formula of

=OFFSET(Cost_Types,0,0,(COUNT(Cost_Types))*($E$10<>""),1)

shows the list if E10 is not blank, no list if it is (still shows the arrow
unfortunately, and does not remove any previous item, but it doesn't
complain when you re-enter, and doesn't allow a blank).

BTW, this assumes the Ignore blank checkbox is unchecked.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Roger, had a slight problem with Bob's which presumably was because
Cost_Types were text

Thanks all

 

[John]

Even better Bob, but can't get it to works, says it evaluates to an error

 

[Roger Govier]

Hi John & Bob

Amazingly, I also had that message when I tried it, but I said to ignore.
I then deleted the value in E10, and tried again and it worked.
I then put a value in E10 and it still worked and continues to work (I'm
baffled - but that's not new<g>)

I changed it to COUNTIF rather than COUNT as per my earlier posting to deal
with both text and numerics.

Regards

Roger Govier

 

[John]

Don't know how, but didn't get the error this time, I just removed the +1
from Rogers as per Bobs and bingo

Thanks Guys

 

[Bob Phillips]

Guys,

I also got that message, but right at the start, and I assumed it was
because that version of the formula didn't work. It certainly works okay
now, and I have just changed the Cost_Types to text, made Roger's suggested
change to the formula (COUNTIF version) and get no problems now.

I have just tried it in a new workbook and I got the same experience as
Roger. I get the error, I ignore it, either clear or add to E10, and it then
works fine. I guess that when you say to continue, it will ignore errors
hereafter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Thanks Roger,

BTW, COUNTA takes numeric or text, just liek COUNTIF.

Bob

 

[Bob Phillips]

As I said in my response to Roger, I think by saying yes to continuing when
it says it evaluates to an error, it assumes that you have made that
decision for evere, not constant nag boxes )

Bob

 

[Roger Govier]

Hi Bob

You're quite right. Somehow I had always thought COUNTA was for text only.
Do you know which is faster, COUNTA or COUNTIF?

Regards

Roger Govier

 

[Bob Phillips]

I don't know for sure, I will benchmark it tonight, but my bet is COUNTA.

--

HTH

RP
(remove nothere from the email address if mailing direct)