VBA: Validate cells in a row [SOLVED]

[inddon]

Hello There,

I have a Invoice entry (attached file) in excel with columns:
1. Invoice Number
2. Invoice Date
3. Quantity
4. Price/Unit
5. ....

What I am looking is the following:

1. the user should be able to enter data in other cells in a row only if Invoice Number and Invoice Date is entered. If any one of them is blank and the user enters data in other columns (eg. Quantity), then it should give a message and take the cursor to Invoice Number.


I was wondering if this could be done using VBA?

Please advise and look forward to hearing from you

Regards
Don

 

[Luke M]

Hello Don,

Don't need to use VBA, unless you really want to. With given example, select cells C2:G7. Go to Data, Date Validation. From dropdown, choose custom, and give a formula of:
=COUNTA($A2:$B2)=2
Hit ok.

Formula will only evaluate to TRUE when both Invoice # and Invoice Date are entered. Formula needs to be true in order for data to be entered into cells, thus accomplioshing goal.

 

[inddon]

Hello Don,

Don't need to use VBA, unless you really want to. With given example, select cells C2:G7. Go to Data, Date Validation. From dropdown, choose custom, and give a formula of:
=COUNTA($A2:$B2)=2
Hit ok.

Formula will only evaluate to TRUE when both Invoice # and Invoice Date are entered. Formula needs to be true in order for data to be entered into cells, thus accomplioshing goal.

Wow!!, that is wonderful Thank you for your solution, it works perfect.

I would like to go a few steps further, if this would be possible without using VBA as well or is this would be too complex.

I have described the requirement in the attached file for your reference.

Please advise.

Regards
Don

 

[Luke M]

Yep, still don't need VBA I believe. Got the check marks working with an IF statement, and some conditional formatting.
Also, since the Amount column is a formula, it will always be there as your table expands, so I removed yellow formatting (user doesn't fill it in), and it's not checked by the 'Row Valid' column. Data validation setup for the Yellow and regular cells, requires both Green cells to be filled. Error message displayed if not.

 

[inddon]

Yep, still don't need VBA I believe. Got the check marks working with an IF statement, and some conditional formatting.
Also, since the Amount column is a formula, it will always be there as your table expands, so I removed yellow formatting (user doesn't fill it in), and it's not checked by the 'Row Valid' column. Data validation setup for the Yellow and regular cells, requires both Green cells to be filled. Error message displayed if not.

Thanks Luke, it works wonderful with all the amounts

When I made the amount 2 column as a Description and entered text in there, the symbols showed a X.

How to make it work irrespective for what you enter (text, numbers, etc.), should be treated as a entered data for that cell? (attached workbook )

Regards
Don

 

[inddon]

Yep, still don't need VBA I believe. Got the check marks working with an IF statement, and some conditional formatting.
Also, since the Amount column is a formula, it will always be there as your table expands, so I removed yellow formatting (user doesn't fill it in), and it's not checked by the 'Row Valid' column. Data validation setup for the Yellow and regular cells, requires both Green cells to be filled. Error message displayed if not.

Hello Luke,

In regards to my previous post:

Instead of COUNT, I replaced it with COUNTA. Now it takes into account the text counts as well.

In the actual workbook, there are existing validation on some fields, therefore I am not able to add new data validations. But at least, I have the checked and cross symbols working.

Thanks a lot for your help and solution.

Regards
Don

 

[inddon]

Hello Luke,

In regards to my previous post:

Instead of COUNT, I replaced it with COUNTA. Now it takes into account the text counts as well.

In the actual workbook, there are existing validation on some fields, therefore I am not able to add new data validations. But at least, I have the checked and cross symbols working.

Thanks a lot for your help and solution.

Regards
Don

Yep, still don't need VBA I believe. Got the check marks working with an IF statement, and some conditional formatting.
Also, since the Amount column is a formula, it will always be there as your table expands, so I removed yellow formatting (user doesn't fill it in), and it's not checked by the 'Row Valid' column. Data validation setup for the Yellow and regular cells, requires both Green cells to be filled. Error message displayed if not.

Hello Luke,

Could you please mark this post as [Solved] for me

Thanks
Don