Cell Validation

[Jim]

I have a cell (D11) validated to a list named Jnl that has two choices , Job
& Ledger.

When Ledger is selected in D11, cell E11 validates to a list of several
codes named LedgerList. When Job is selected from the validation list D11,
cell F11 shows a list of only two codes from a list named CAT.

I use the formula =IF(D11="Ledger",LedgerList," ") as the validation for E11
and =IF(D11="Job",CAT,"") for F11. The validation will not display "" ,or
blank, when the Acc Type is changed. The last selected code remains.

Is there a better way to have E11 display a blank (empty) when the Acc Type
is changed to Job in D11 and allow the selection of the CAT list in F11 and
visa-versa?

 

[Sandy Mann]

Your validation works the way you want it for me in XL97.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Stephen]

I have a cell (D11) validated to a list named Jnl that has two choices ,
Job
& Ledger.

When Ledger is selected in D11, cell E11 validates to a list of several
codes named LedgerList. When Job is selected from the validation list
D11,
cell F11 shows a list of only two codes from a list named CAT.

I use the formula =IF(D11="Ledger",LedgerList," ") as the validation for
E11
and =IF(D11="Job",CAT,"") for F11. The validation will not display "" ,or
blank, when the Acc Type is changed. The last selected code remains.

Is there a better way to have E11 display a blank (empty) when the Acc
Type
is changed to Job in D11 and allow the selection of the CAT list in F11
and
visa-versa?

I'm not quite sure I understand, but I think the following comment may be
helpful to you. Data validation works only at the instant you enter data
into a cell. It is not a "live" thing, like the formulas in cells (assuming
calculation is set to automatic). So, if a cell has data validation applied,
Excel will check the data is valid as it is being entered. But, if data
elsewhere changes such that it subsequently becomes "invalid", nothing will
happen at that point - the data as previously entered will remain.

If I read your requirements correctly, I think you will need an
"intermediate" cell with data validation (based on LedgerList). You could
then have a simple IF formula in D11 to select between this and "" depending
on what is selected in D11. (You would repeat all of this with suitable
adjustments to derive E11.) As far as I can see, you cannot successfully do
the data validation and selection in one cell.

 

[Jim]

Thanks for your comments.

I had arrived a much the same conclusion. I will set it up as an empty
template so the order of precidence should ensure that the first choice
excludes the second and will be blank as default.

Cheers
Jim

 

[Jim]

Thanks Sandy, I was hoping for the usual magic I get in this forum.

Cheers
Jim
In Perth, the not so ancient capital of Western Australia!