Solved

Excel If tests

Posted on 2016-10-20
10
72 Views
Last Modified: 2016-10-25
So, I would like to do some real Excel kung Fu.  I have to evaluate A LOT of excel documents.  The formatting has to be exactly right.  I want to make a tab that I can copy into the doc, that will run a bunch of tests to check the data and formatting.  

Like (For Exact match's) (this works fine)
=IF(A4=Sheet1!A2,"The Value is Exact","The value is NOT exact")

But the more complicated ones, I am not sure how to check;
Is this a Date in specific format mm/yyyy or mm/dd/yyyy
Is this a number
Is this a WHOLE number (no decimals) (FIXED)
           =IF(INT(SHEET1!L3)=SHEET1!L3,"Is a whole #","Is NOT a Whole #")
Is this a Number with 2 decimals
Does this start with a 'p'
Does this have a '.' (period) in it anyplace)
Does this SUM to 1000
Does A1= any value in a list (column or something)
Is the cell formatted as number/text/general

One last thing, when I copy to a new sheet, it keeps trying to reference the old file like;
=IF(A5=[20161020_Tester_Tab.xlsx]SHEET1!A1,"The Value is Exact","The value is NOT exact")

More awesome, How would I have it check MORE then just one value, but a whole set of values A3-(Last value)

Please save me days and weeks of work, and help me out :(
Thank you in advance!!
0
Comment
Question by:loftyworm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41853215
for a series of validations, you need to determine:
* a list of validation rules
* the sequence of validation rules

if the validation rules is too long, you would probably not able to enter all rules in the cell's formula, and hence you probably may do the validation in Macro function instead. and then call it when you want to do the validation.

One last thing, when I copy to a new sheet, it keeps trying to reference the old file like;
that's the default behaviour how Excel works. You will need to change the formula manually if you want it to refer to the same workbook.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 41853282
I would suggest doing it in vba macro too. A bit harder if not familiar but would be portable to other sheets, just have it work down a range of cells or sheets.

Done if the above such as checking date formats and numbers with two decimal places etc are only related to the formatting if the cell as the data would be a date or number but could be formatted to show 21-oct. 21/10/16 or whatever and value of a number could be 1 but down as 1.00 or conversely 12.542575 but down as 13

How many checks are we talking here?

Steve
0
 
LVL 11

Author Comment

by:loftyworm
ID: 41853833
TY!!
Can I get some more direction on the specific macros.  I have zero experience with them (other then disabling them for security)

The tests will be 1 per cell, not all at once.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Steve Knight
ID: 41854027
Suggest give us a example sheet with some data in it and can give some examples.

Steve
0
 
LVL 11

Author Comment

by:loftyworm
ID: 41854051
Sure, one sec
0
 
LVL 11

Author Comment

by:loftyworm
ID: 41854113
Sorry about the delay, got called by boss.
Here is an example of what I need to test

Ideally (Open to suggestion) I would think I would take the tab, and copy it to the FILETOBETESTED.  I have had problems with this though, as the tab remembers the file name, so I may have to copy the data to the tester file.

I know how to do the color formatting so that all the TRUE values would be green, and all the bad values red.  I just need the tests.
C--Temp-Book1.xlsx
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 500 total points
ID: 41854225
A quick example bit of VBA macro is below and attached in spreadsheet.  This runs down the cells you have selected and based on the column it checks for various formatted data.  In this case then colours them in red or green whether they pass or not but could do whatever else was needed.

You could have a master sheet which you paste the values into, you could point it at a data sheet and have it copy the data out as it is displayed and copy it... anything possible but depends more on what you want to do.  

At the moment it checks the value displayed in the cell (cell.text) rather than checking the actual value (cell.value) or how it is formatted.

Steve

Sub CheckData()

Dim myarea As Range
Dim ans As Integer
Dim CheckCell As Boolean
Set myarea = Selection.Cells

ans = MsgBox("You have highlighted " & myarea.Cells.Count & " cells.  Is this right to continue?", vbYesNo + vbQuestion + vbDefaultButton2, "CHECK WHICH CELLS")
If ans = 7 Then Exit Sub

For Each col In myarea.Columns
    For Each cell In col.Cells
        CheckCell = True
        Select Case cell.Column
            Case 2 ' Column B - check if specific strings
                Select Case cell.Text
                    Case "bob", "steve", "mary": CheckCell = True
                    Case Else: CheckCell = False
                End Select
           
            Case 3 ' Column C - check if mm/dd/yyyy format
                If Not cell.Text Like "##/##/####" Then CheckCell = False
               
            Case 4 ' Column D - check if specific string value
                Select Case cell.Text
                    Case "2Car1House", "1Car2House": CheckCell = True
                    Case Else: CheckCell = False
                End Select
           
            Case 6 ' Column F - check if Integer of value is same as value
                If Not cell.Text Like "*#" Then CheckCell = False
           
            Case 10 ' Column J - check if it looks like number pattern shown
                If Not cell.Text Like "*#.##" Then CheckCell = False
        End Select
        Select Case CheckCell
            Case False: cell.Interior.Color = 255
            Case True: cell.Interior.Color = 5296274
        End Select
    Next
Next

End Sub
C--Temp-Book1.xlsm
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 41859114
Thanks for choosing my answer but if you want any extra help or clarification just say.  There are lots of ways of doing things like this of course.

Steve
0
 
LVL 11

Author Comment

by:loftyworm
ID: 41859439
0
 
LVL 11

Author Comment

by:loftyworm
ID: 41859441
Oops, I think maybe you have!!  This is what I get for doing to many things at the same time
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question