Data Validation

Hello all,

I am trying to add validation in an excel template for my official use.
And I am in need of support from Excel Experts.
I hope, I am in the right forum to solve my issue.
I am using MS Excel 2000 in office.

Issue 1
Data in the cell K8 should take a range as per cell C1's input.
When I set cell C1 at 100, K8 range to be restricted between 1 to 10.

And, I have to set different values at C1
C1 Range at K8
100 Range 1 to 10
200 Range 11 to 20
500 Range 51 to 60 and 81 to 90
Only for 500, it is not a continuous range.

Issue 2
Cell H1 and J1
J1 value should be equal to or greater than H1.
In both the fields, data can be entered manually.

Issue 3
Three cells are there in this logic (H1, J1, K1).
Continuation to issue 2.
K1 data is predefined with a drop down list (RF, NP, AR).
And K1 may be keep as empty cell also.
If K1 is having some data from the dropdown list, data in cells H1 and J1 should be reset to zero.
If K1 is empty, logic 2 applies to these cells.

I request the experts to help me to solve these issues.

Originally Posted by sudheesh.e

Hello all,

I am trying to add validation in an excel template for my official use.
And I am in need of support from Excel Experts.
I hope, I am in the right forum to solve my issue.
I am using MS Excel 2000 in office.

Issue 1
Data in the cell K8 should take a range as per cell C1's input.
When I set cell C1 at 100, K8 range to be restricted between 1 to 10.

And, I have to set different values at C1
C1 Range at K8
100 Range 1 to 10
200 Range 11 to 20
500 Range 51 to 60 and 81 to 90
Only for 500, it is not a continuous range.

Issue 2
Cell H1 and J1
J1 value should be equal to or greater than H1.
In both the fields, data can be entered manually.

Issue 3
Three cells are there in this logic (H1, J1, K1).
Continuation to issue 2.
K1 data is predefined with a drop down list (RF, NP, AR).
And K1 may be keep as empty cell also.
If K1 is having some data from the dropdown list, data in cells H1 and J1 should be reset to zero.
If K1 is empty, logic 2 applies to these cells.

I request the experts to help me to solve these issues.

Issue 1

Create 3 lists with the numbers 1:10, 11:20, 51:60 & 81:90

In this example they are in columns A,B & C starting row 11.

In K8 pick the Data Validation List option and enter the following in the Source box:

Please Login or Register  to view this content.

Issues 2 & 3 seem inconsistent. To meet the requirements of issue 3, H1 & J1 require a formula, yet at Issue 2 you say H1 & J1 can have data entered manually. You can't do this and still retain the formula.

Or maybe I've misunderstood. Can you clarify please?

Hello Richard and Portuga,

Thanks for your responses.

Issue 1
I have tried with the solution given by Richard.
I have added validation for Cell K8.
But the data restriction is not happening.
I am able to enter any numerical data in K8.
I have attached the Excel file for your reference.

=IF(C1="";"";IF(C1=500;List500;IF(C1<100;"";IF(AND (C1>=100;D1<200);List100;IF(AND(C1>=200;C1<300);Li st200)))))
As Portuga said, my Excel file is not accepting the equation with "List500 or List100 or List200".
For Excel 2000, I have replaced all ; to ,

Issue 2
It is working perfectly and this closes this issue.

Issue 3
=If(H2>0;0;"")
Cell J1 is already having a validation for Issue 2.
And manual data entry should be possible for cells H1 and J1.
In this situation, how can I add =If(H2>0;0;"") for cells H1 and J1.

Kindly advise me solution for these issues.
If you require any additional information, please let me know.

Regards,
Sudheesh

Originally Posted by sudheesh.e

Hello Richard and Portuga,

Thanks for your responses.

Issue 1
I have tried with the solution given by Richard.
I have added validation for Cell K8.
But the data restriction is not happening.
I am able to enter any numerical data in K8.
I have attached the Excel file for your reference.

=IF(C1="";"";IF(C1=500;List500;IF(C1<100;"";IF(AND (C1>=100;D1<200);List100;IF(AND(C1>=200;C1<300);Li st200)))))
As Portuga said, my Excel file is not accepting the equation with "List500 or List100 or List200".
For Excel 2000, I have replaced all ; to ,

Issue 2
It is working perfectly and this closes this issue.

Issue 3
=If(H2>0;0;"")
Cell J1 is already having a validation for Issue 2.
And manual data entry should be possible for cells H1 and J1.
In this situation, how can I add =If(H2>0;0;"") for cells H1 and J1.

Kindly advise me solution for these issues.
If you require any additional information, please let me know.

Regards,
Sudheesh

Hi,

In the file you sent, the Data Validation for K8 was using the 'Custom' option in the allow list. You should be using the 'List' option.

Rgds

Hello Richard,

Thanks a lot.
I have selected List option and it is working fine.
Sorry, this is a mistake from me.

I have one more question in the same issue.
Is it possible to call these three lists from other worksheet (of the same file)?
Actually, I want to protect the lists with a password and hidden.
This file is going to be used by multiple users and they should not change this list.
Please advise.

About issue 3, do you have any suggestion to implement?

Regards,
Sudheesh

Originally Posted by sudheesh.e

Hello Richard,

Thanks a lot.
I have selected List option and it is working fine.
Sorry, this is a mistake from me.

I have one more question in the same issue.
Is it possible to call these three lists from other worksheet (of the same file)?
Actually, I want to protect the lists with a password and hidden.
This file is going to be used by multiple users and they should not change this list.
Please advise.

About issue 3, do you have any suggestion to implement?

Regards,
Sudheesh

Hi,

Unless I'm missing something I don't see how you can have resolution for issue 3. You said,

"And manual data entry should be possible for cells H1 and J1.
In this situation, how can I add =If(H2>0;0;"") for cells H1 and J1."

I don't see how you can allow both manual entries in J1:J1, and retain a formula in those same cells.

The only possible solution is one involving VBA and a Worksheet Selection_Change event, such that if you manually enter something in either cell, the other has the formula added to it. But this may not be what you want.

Rgds

Hello Richard,

I hope you are clear with my requirement.
This is the logic I want in Excel.
Right now, we are entering the data manually in all three cells (H1, J1 and K1)

If we can implement this logic with VBA, I am OK with that.
Could please guide me with the steps.

And for the list validation (Issue 1), is it possible to call the validation from another worksheet. (Please refer my previous post). I think data validation cell references should be in the same worksheet.

Please advise.

Regards,
Sudheesh

Originally Posted by sudheesh.e

Hello Richard,

I hope you are clear with my requirement.
This is the logic I want in Excel.
Right now, we are entering the data manually in all three cells (H1, J1 and K1)

If we can implement this logic with VBA, I am OK with that.
Could please guide me with the steps.

And for the list validation (Issue 1), is it possible to call the validation from another worksheet. (Please refer my previous post). I think data validation cell references should be in the same worksheet.

Please advise.

Regards,
Sudheesh

Hi,

No, I don't believe you can Refer to validation lists from another w/s.

I'm stil not clear exactly what you want to achieve with your three cells. So far I understand that you want to both add data manually, but also hold a formula in there. If you can set out the rules that apply. i.e. do you want to add data to two cells and have a formula in the third cell, or add data to one cell and have formula in the other two, and in which case what is the formula.

Rgds

Hello Richard,

I have three cells to check (H1, J1 and K1).

H1 and J1 - Manual data entry (only numerical data)

J1 is having a data validation =J1>=H1.

K1 is having a dropdown list (AR, NP, RF) with ‘Ignore Blank’ option not selected (can be keep as empty cell).

Now, my requirement is,

If K1 is having the data from the dropdown list (AR, NP, RF), H1 and J1 data to be reset to zero else keep it empty.

And if K1 is empty, data in cells H1 and J1 may hold good (validation in J1 should work).

Hope I clarified your doubts.

Please help me to get a solution for this logic.

Regards,
Sudheesh

Hello Richard,

Still I am waiting for your update.
If we cannot implement this condition in Excel, please let me know.
Then, I have to exclude this from my template upgradation plan.

Regards,
Sudheesh

Originally Posted by sudheesh.e

Hello Richard,

Still I am waiting for your update.
If we cannot implement this condition in Excel, please let me know.
Then, I have to exclude this from my template upgradation plan.

Regards,
Sudheesh

Hi,

I fear we are going around the proverbial circle.
Your requirements are as I understand it.

H1 and J1 - Manual data entry (only numerical data)
J1 is having a data validation =J1>=H1.
K1 is having a dropdown list (AR, NP, RF) with ‘Ignore Blank’ option not selected (can be keep as empty cell).

If K1 is having the data from the dropdown list (AR, NP, RF), H1 and J1 data to be reset to zero else keep it empty.

And if K1 is empty, data in cells H1 and J1 may hold good (validation in J1 should work).

For H1 & J1 to have values which depend on K1, then implicit in that is the fact that H1 & J1 must both contain an IF() formulae. Which is inconsistent with your other statement that H1 & J1 have manual entries. As I said very early on, you can't have both a formula and then enter a manual entry in the same cell and still retain the formula.

In short, and without VBA, (and of course providing I understand your requirement), I don't believe you can achieve what you're wanting to do.

Regards

Hello Richard,

Thanks for support.

This is the condition I would like to have in my Excel template.
Right now, we are checking this criteria manually.
If it is not possible, I will exclude it from my list.

In future, if you get a solution for this, let me know.

Once again, thanks for your support.

Regards,
Sudheesh