Formula/Validation help 1

[jlancton]

Hello,

We have a spreadsheet for the daily census of our nursing home. For each category, Admission, Discharge, Death, etc. we use data validation to produce drop down lists for unit name, etc to prevent incorrect data entry. What we need is a way to prevent entry (or selection from a drop down) based on the value of another cell. For example, if a resident had been discharged to home, hospital, etc, and later expires, the entry of their death on the census cannot have a unit associated with it because they were already out of house. So how do we tell Excel to prevent the user from selecting a 'unit' from the drop down, if the value of the 'where' cell is not 'in-house' ?

Any help will be greatly appreciated.

Thanks,

-Jeff

 

[Dreamboat]

You mean something like this?

http://www.vbaexpress.com/forum/showthread.php?t=257

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121

 

[GlennUK]

There is a way to do this by using INDIRECT in the validation for the 'unit' field.

Assuming that the list for unit is stored in a group of cells, you need to name these cells the same as each 'where' choice (i.e. add a defined name for these cells multiple times ) except for 'in-house'.

Then for the 'unit' data validation use "List" option with a definition of :

=INDIRECT(cellforwherechoice)

using the cell reference for the 'where' choice inside the INDIRECT function.

Hope this works for you. ( it may need some tweaking if you have 'where' entries that are not acceptable Excel names )

Cheers, Glenn.

 

[jlancton]

Thank you Glenn! That did the trick, although I had to do it sort of backwards. If I named the list of units four times, for each of the other possible 'where' choices, and used the indirect reference, it would allow me to select a 'unit' for any choice that wasn't 'in-house', and prevent selection when it was, which was the opposite of the desired effect.

So I named the range for 'where' just once, using 'in-house', and then did indirect to the 'where' cell, and it works. If that cell is 'in-house' you can select a unit. If it's anything else, you can't.

Thanks again!

-Jeff

 

[GlennUK]

Glad you got it working. I obviously misread the bit about "not" in-house.

Cheers, Glenn.

 

[jlancton]

Hi again,

The indirect is working great. I'd like to use it in another cell, but it's doing something strange, and I'm not sure I understand why.

There are three cells, in order:

Unit Where BedHold

Currently, the Unit cell uses Indirect to only display the unit dropdown if the Where = "SMNH", which works. What I'd like is to have the BedHold cell (which is a Yes/No dropdown) only be displayed if the Where cell is NOT "SMNH", since if the person is inhouse, there would be no BedHold. I tried using Indirect back to the Where cell, and if Where is not "SMNH", I can select Yes or No, but if Where is SMNH, the BedHold cell displays the Unit list in the dropdown. Strange.

It's surely an incorrectly named range, but I'm not sure if I can indirect back to the same cell twice with different named ranges.

Any help will as always be greatly appreciated.

-Jeff

 

[GlennUK]

Hi there,

try this. Create a list containng Yes and No ( a pair of cells with these contents ), and name the range of cells as NOTSMNH.

Then use this formula in the list specification for the BedHold Data Validation :

=INDIRECT(IF(B2="SMNH","","NOTSMNH"))

and it should behave the way you want ( B2 in this example refers to the corresponding "Where" cell for the "BedHold" I was creating the Data Validation for ).

Good luck.

Cheers, Glenn.

 

[jlancton]

And another Start for you! That wokrs great!!

Thank you!!