Avatar of Steve B
Steve B
Flag for Australia asked on

Validations Required on Excel Spreadsheet

I have a spreadsheet that has had a few macros added to it by one of the amazing experts on the site.  

What I require are the following validations to occur.

In Column E, F and M it often will have different values inserted into them, so they do not need to be equal.  

Column M again will often have a separate value to column E or column F to determine the charge.  The value in Column M is based on the following factors and they are ...

1

If column C contains the letter E,

THEN

2

Column E has the value INTERNATIONAL or DOMESTIC, then the value in column M will be equal to the value in Column F and the charge will be based on the value of column F.

ELSE

3

If column F has the value of INTERNATIONAL or DOMESTIC, then the value in column M will be equal to the value in column E and the charge will be based on the value of column E.



I hope the above makes sense.

With regards to column R, T, V, X and Z, the list of values that should appear there can be found in the Worksheet "Zones + Tolls" column D and column G.


The values found or to be used are all in the worksheet "Zones + Tolls".

I hope this all makes sense.
01-Invoice-Template-blank.xls
Microsoft ExcelSpreadsheetsVB Script

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Several questions:

1.    At what point do you want the validations to happen? In other words how will I know when the user is done entering his data so that it can be validated?
   
2.    It looks like the user can enter anything he wants in column C. Are there actually a set of valid entries that we could add to Zones + Tolls?
   
3.    Do you want any other kinds of validations, like, say, validate that A13 is a date?
   
4.    It looks like you would not want the user to manually add some amount in I13, O13, etc. Are there cells that the user should never be able to change? If so let me know because the sheet can be set up to restrict access to those cells.
Martin Liss

After looking at your question more closely it seems to me that you are stating the requirements for a formula rather than a validation. When I talk about validations I mean something like if the user where to enter "blah" in cell A13 there would be an error shown that says something like "Date must be a valid date in YYYY/MM/DD format". Let me know if I'm missing something in your requirements.
Steve B

ASKER
Hi MartinLiss,
1) The validations should almost occur after the user presses TAB or ENTER.

2) The user has to enter the JOB number in column C.  This job number changes regularly and there is really no set of constant entries.

3) No other kinds of validations are required.

4) No, all cells need to be able to be manipulated or changed.  column I13, has its own formula that calculates the difference in time from Column G and H and Column 013 calculates the charges based on a number factors.  I have already entered a formula that works perfectly here, so no need to make changes.

5) What I basically would like to have is that when Column E has the value of INTERNATIONAL or DOMESTIC in it, then COLUMN M will equal the value of column F.

If Column F has the value of INTERNATIONAL or DOMESTIC in it, then column M will equal the value of column E.

If Column E or F does not have the value of INTERNATIONAL or DOMESTIC then Column M remains blank.

I hope this makes sense.  

I have been trying to write a formula or validation (whatever they are called) so that this happens, but I can only get one part of the 3 part process working.

Your help would be greatly appreciated.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Martin Liss

2) It seems that what is entered in column C has an effect (which I don't understand yet) on columns E and F. I gather that the effect is triggered by a cell in that column containing an "E". So if I entered "It's been a long day, I want a BEER" what would you like to have happen?

3) and 4) Consider this. You have a formula in cell I13, but if I choose to do so on purpose or by accident I can enter anything I wish including something like "blah", wiping out the formula. If that's okay then you're fine but if it's not you should not allow the user to touch that column.

Also what do you want to do if both columns E and F have "DOMESTIC" or both have "INTERNATIONAL" or one has one value and the other has the other value?

Finally, validations (if any) can be done after Tab or Enter but what if I merely move the cursor after entering something? Better I think would be to do the validations in the Worksheet_Change event for the sheet which would catch every change except the last one when the user is done and prints the invoice or whatever he does with it. IMO the best way to do validations is via a button on the invoice where every cell would be validated at the same time. Your users would of course need to be instructed to press that button. In another EE question I've been working on, I've developed a validation process that highlights problem cells in yellow and adds a comment specific to each error in in yellow cell. Here's what that looks like. The message is not shown unless the cursor is on the problem cell.
An error
Steve B

ASKER
2) Yes you are correct about this.  We have four separate job numbers.  Each Job number in column C will have an identifying letter. If the value in column C contains the capital letter E, then this so called process is actioned.  The job numbers will always start with a letter. For example E00657 or E887610 or U5570 etc. So while you can write BEER or something, my staff are well trained and so they know what should be entered in this column. I have not had any of these issues before.

3) The formula found in cell I13 is always calculating the time difference between the other cells. I am not concerned about locking/restricting this cell. It has not happened before and therefore we can keep it as is.

Also what do you want to do if both columns E and F have "DOMESTIC" or both have "INTERNATIONAL" or one has one value and the other has the other value?
This rarely happens. In actual fact in the last two years, I can only recall this happening once, so if both columns equal DOMESTIC or INTERNATIONAL or one of each in each column, nothing should happen. We can manually enter the value in the other cells.

With regards to validations, we won't require any of them.
Martin Liss

Okay so I guess what were saying here is that all you need is the formula you originally described. While I'll give it a shot, I probably can't help you with it. In any case here's a new workbook that includes the following:

Removed the drop downs from rows below the invoice where they didn't belong
Removed white text data from below invoice in columns E, F, R, T, V, X and Z
Turned off background error checking (the green triangles) in the Invoice Unity Chauffeur sheet
Made the floating combobox wider in columns E, F and M

I also added a ChangeLog module which can be used to track future changes to code. If you look, say, for change "2B" you'll find this

        '******* 2B Start *******
'        .Width = Target.Width + 15
        Select Case Target.Column
            Case 5, 6, 13
                .Width = 120
            Case Else
                .Width = Target.Width + 15
        End Select
        '******* 2B End *********

Open in new window

The comments were easily added using an add-in I wrote. You can read about it here if you're interested.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve B

ASKER
I can't seem to find the file to download.  You may have forgotten to attach the document.
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve B

ASKER
Okay, thanks for doing that.  Not what I was really expecting, but then again, I guess we were a had a slight mis-interpretation of the requirements for this Question.

Still a great job.  Thanks.
Martin Liss

Again I'm glad I was able to help (a little).
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

In the middle of the night it dawned on me that what you want can be accomplished via a user defined function and here is a version of your workbook that contains one called JobNo. You will find references to the function in column M.
01-Invoice-Template-blank-V3.xls
Steve B

ASKER
Thank you for still working on this.

I have noticed a few problems however.

1) The only cell that seems to work with "defined function" is C13.  All the other cells don't seem to function properly.

2) C13 works on the formula if the letter C is found in Column C.  This should be the letter E.

3) The undo option seems to not work after pressing TAB or ENTER.  This also occurs in version 2 of the template.  Can this be fixed, so that the UNDO option is always available incase a mistake is found after completing a row or two.

I have attached v3 template with what I have done so you can see what I mean.
01-Invoice-Template-blank-V3.xls
Martin Liss

I changed the UDF (User Defined Function) to look for "E" in col C rather than "C".
To me it seems that all cells do work. I change the value in cell C16 as a part of my testing.
What "UNDO" option are you referring to?
01-Invoice-Template-blank-V4.xls
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve B

ASKER
I am referring to the UNDO option that lets you undo a typo error etc.  It is the little SEMI-CIRCLE you find in top left corner of the Ribbon, the shortcut being Ctrl+Z.

I can't seem to get the function working.  Regardless of whether I type the letter E or even change what you have saved.  It doesn't seem to work.  I have Office 2013, but I don't believe this should be a problem.

Is there something I am doing wrong.
Martin Liss

I never knew about that Undo problem until today but I did some research and it seems that it happens in every workbook that contains event or macro code.

I can't reproduce your problem in Office 2010 so I'm providing a link to a dowload of a movie I recorded of what happens if I change cell C15.

If that doesn't work the same in your case then you'll need to ask someone who has 2013.
Martin Liss

I'm glad I was able to help.

Marty - MVP 2009 to 2013
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23