Resetting a pull-down list field to a BLANK

For have several buttons in this form but for a variety of reasons I do not want to use a button Using button requires someone it when appropriate. The first rule of life is “KISS.”

I have a form that consists of several worksheets on the first worksheet I ask user to select their gender from a pulldown data validation list “F - Female, M - Male, L - Lesbian, G - Gay, Q - Queer, T - Transgendered or N - Non-binary.” If their response is either “F – Female” or “L - Lesbian” in another part of the worksheet I ask only Females and Lesbians if they are pregnant. If they are pregnant later, I ask then to select the month of their pregnancy. Since all women are not pregnant all the time the pull-down list includes “No.”

This workbook may be used by several different member of same family when collecting medical histories.

I have written instructions that if the workbook has been edited to save their workbook under a different filename; otherwise all their data will be erased.

BUT I believe that I must plan for this scenario: A woman in her 5th month of pregnancy completes only a part of workbook. Has to stop entering data goes wherever.

She has told her partner that he needs to complete his own form. He returns home after picking up kids after swimming practice at their local YMCA. Sees the form, sits down at the family computer and completes either part or the whole form. Overwriting the existing entries but not editing the Pregnancy month field since the field caption has been changed to “Not Applicable”. The caption for Females and Lesbians is “Month of Pregnancy.”

I reset several fields that are applicable only to a pregnant woman to “Not Applicable” but have been unable to reset the Pregnancy Month field with its data validation list where the user selects the month of their pregnancy from a pull-down menu list. The consists lists of No, 1, 2, 3, 4, 5, 6, 7. 8. 9. “No” is an answer because not every woman is pregnant all the time.

How can I reset the Month of Pregnancy field with pull-down menu to a blank?

Hi - can you post a small sample of your file? Easier for us to see what you have and figure out a solution.

Thanks

Mike,

Thank you for your response. I created a file called Example.xlsm cannot find a button to upload the file. How can I upload the file?

Ed

I have uploaded a file named. Sample.xlsm

OK, not sure if this is really what you want or not, but the only solution I could think of.

First I created another sheet called Data and listed the conditions for Data Validation. Y or N, then the number of months, and then a separate No for your scenario.
I always create Names for each list I am working on when I do Data Validation. If you look under the Formulas Tab and look at Name Manager you will see them listed YN is for Y or N, Months is for 0, 1, 2, up to 9, and NA is for No.

If you look at the Data Validation for B6 you will see the formula I put there.

So the bottom line is that if A6 shows Not Applicable, you only have to select No (the other months won't show up)

Let me know if you have any questions.

Mike,

Sorry to be so dilatory in responding to your proposed solution. However, not suitable for church folks. It's too complicated.

Thank you very much for all your efforts in trying to find a solution,

Ed

Sorry if you think it's complicated. I can give you a detailed step-by-step guide to set it up.

Remember, once it's all set up, then you're good to go for the future. It's not like you have to do this every time.