Excel If tests
Posted on 2016-10-20
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!!