data validation question

NBVC, you're my Excel hero! Thank you again! Two last questions...well until I think of the next one.

First, if a person enters "yes" in cell C1 (which will allow input in D1), can I make D1 a drop down box as well, or am i only allowed 1 data validation per cell. It would be very helpful if I could make C1 a dropdown "yes or no" and then let D1 be a dropdown box be "strawberry, rocky road, other" (i don't want them to be able to put in Mint)...see what I mean?

Second, the spreadsheet I'm creating will have varied data from month to month (I'm making a spreadsheet to track if cancer patients go on a clinical trial or not). The number of patients the office sees varies from month to month. What'd I'd like is the totals to be on the last line on the sheet and what I'd like to avoid is 'X' amount of blank lines before getting to the totals. For instance:

In January we saw 50 patients and 15 of them went on a clinical trial. So, I can use your handy formula on lines 51 and 52. If in Feb the office sees 30 patients, I'd rather not have 20 blank lines before it gets to the totals. March we saw 65 patients, I'd rather not reformat that spreadsheet from month to month.

Does what I'm asking make sense? Is it possible?

Without getting into some VBA macros to find the last row and insert formula, you can't do that too easily.

My suggestion would be to insert a row at the top of the sheet and place the formulas there that will summarize your data.


My previous formula(s) could be ammended to consider entire columns,

e.g. =COUNTIF($C:$C,"yes")/COUNTA($C:$C)

will count all yeses in column C and divided by number of cells containing any values in column C.... you would just subtract, from the divisor, the number of rows at top that contain headings, etc. so that you only account for the rows containing yes or no entries.

yes, you can do that


First create the list somewhere in the spreadsheet in a single column.

Then select that list and go to Insert|Name|Define and in the field at the top (names in workbood) enter the word "yes" (without quotes). Click OK

Now select any blank cell (that won't ever be used) and repeat the naming process above calling that cell "no"....again no quotes.

Now select D1 (cell next to your yes/no dropdown) and go to Data|Validation and select List. In the source field enter formula: =INDIRECT(C1) and click Ok.

Now, when user selects yes in C1, he gets a list of flavours in D1. If he selects no in C1, he gets a blank list.

No problem.

Good Night and Good luck.