Cell Validation only if previous cell not blank 1

[JohnOB]

I have a spreadsheet I am using as a form, with validation to allow in cell drop downs. We would like the form to be setup in a way that the users could not fill it out in case the previous section had been completed.

Is it possible that the cell could be blank, or locked, until a particular cell had been completed, and once this cell was completed the cell would then show the list from the validation.

Thanks

"Stupid isn't not knowing the answer, it's not asking the question

 

[SkipVought]

Hi,

Yes, it is possible, but not without VBA code.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CommonOddity]

Like SkipVought suggested, VB code would be the way to go.

A few IF statements (and although a while loop might make this easier I'm not sure if it wouldn't eat up too much processing power) and you should have it setup. I can't recall the exact syntax of VB since I've been spending most of my time with Java and PERL.

However the programming logic should go something like this. This will be written with Java syntax (Bit rusty, hehe, I spent a few months recently with PERL even more than JAVA.) although the logic should be just about the same.


int i = 0;
previouscell = cellA$i$;
if(previouscell.value = "")
i + 1;
cellA$i$.value = "";
i - 1;
Else if(previouscell.value != "")
i + 1;
End if

Really shoddy programming, I know- but the general idea remains the same. I'm not sure what would be the best way to implement a loop or timer that checks on this consistently without chewing up too much resources. Someone else would probably have to provide that.

Hope this helps.

 

[SkipVought]

Again, this is not the forum to discuss code issues.

Please post in Forum707 both to post and answer code-related issues.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Hi JohnOB

Is it possible that the cell could be blank, or locked, until a particular cell had been completed, and once this cell was completed the cell would then show the list from the validation.

I know that I've done this before, but I don't want to give an answer until I've confirmed it by using Excel myself ... and I'm on holiday at the moment ... I'll be back the day after tomorrow, and I'll create an example for you then.


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[yogia]

Hi JohnOB:

How about considering using the validation DropDown for the starting value of a row of entries ... and the remaining entries of the row could be formulas where the formula will result into a blank if there is no entry in the starting value of the row. I know it sounds pretty convoluted, but once you grasp it, it should do what you are looking for ... I think!



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[GlennUK]

You have a dynamically defined name for the list source, and have it evaluate to an error when your particular cell is blank. Like this: do menu command Insert/Name/Define and create a name of mylist, with a definition of:
=OFFSET(list_header_ref,1,0,IF(ISBLANK(test_cell_ref),0,3),1)

... assuming that you'll replace list_header_ref with the cell ref of the cell above the list, and that you'll also replace test_cell_ref with the reference of the cell that must be non-blank for the drop-down to work. The ",3" in the formula means that the list has 3 entries ... adjust this as required.

Now set up your Data/Validation, to be a list, with a source of =mylist
You'll probably get an error like:
"The Source currently evaluates to an error. Do you wish to continue? Yes, No." ... press the Yes button.

That should do it. The drop-down won't work until an entry has been made in test_cell.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Smooth, Glenn! ==? [purple]*[/purple]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Thanks Skip! I tend to remember those solutions that don't need VBA

Cheers, Glenn.

Beauty is in the eye of the beerholder.