Link to home
Start Free TrialLog in
Avatar of Steve B
Steve BFlag 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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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.
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.
Avatar of 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.
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.
User generated image
Avatar of 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.
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.
Avatar of Steve B

ASKER

I can't seem to find the file to download.  You may have forgotten to attach the document.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.
Again I'm glad I was able to help (a little).
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
Avatar of 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
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
Avatar of 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.
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.
I'm glad I was able to help.

Marty - MVP 2009 to 2013