Dynamic Data Validation based on series in a single cell

**ExcelLefty** · 09-28-2009, 12:16 PM

I'm new to the forums and have done some searching with no luck, so if I have missed this solution in another post, please forgive the error.

I have a series of numbers listed in a single cell ("Submitted"), separated by spaces. The series can be any combination of numbers from 1-32 and will be entered by the user. I want this cell to be the baseline of possible values that can be entered in 3 other cells labeled "Approved", "Disapproved" and "Discrepancy".

The values listed in "Submitted" can only be used once, so "1" should never be entered in "Approved" and "Disapproved" at the same time. Numbers not listed in "Submitted" should not appear in "Approved", "Disapproved" or "Discrepancy."

Please see attached file for examples.

**Ron Coderre** · 09-28-2009, 01:15 PM

I'm not quite sure of what you ultimately need, but I came up with this:

1) Users enter 2-digit numeric text: 01 03 05 etc
...So all input cells are formatted as TEXT

2) I reference a lookup list of values 01 through 32

For each data set:
• One cell calculates if any two rows contain the same VALID value
• Another cell calculates if any INVALID values were used.

I attached an edited version of your file.

Is there anything in there that you can use?

**ExcelLefty** · 09-28-2009, 04:40 PM

This is close - any chance it can be done without converting the entries to text values?

**Ron Coderre** · 09-28-2009, 05:51 PM

Possibly, but I suspect the formulas will be horrendous.
Without using 2-digit values, more rigorous testing functions are necessary to avoid complications...otherwise, checking if the cell contains "1" would match on 1, 10, 11, 21, and 31. I recommend working with Excel in this case...instead of against it.

If you posted a generic example, perhaps your actual scenario might be easier to accommodate.

**ExcelLefty** · 09-29-2009, 08:45 AM

I was afraid of that, but had to ask anyway. I will see what I can do to make this solution work. Unfortunately, due to confidentiality reasons, I am unable to post the actual scenario. Many thanks for your help.