Excel Data Validation Question 2

[Scott24x7]

Hi,
I have an Excel file that I allow a user to enter a value with a measure, such as 1d for 1 day, or 2w for 2 weeks (I allow h,d,s,w,m in all). However, I don't want to allow the user to enter in numbers other than a whole number. I wanted to build something in the data validation that would parse the value into two parts (ignore the measure on the right) and only allow it as a "valid" entry if the part on the left is a number without decimal value. I would appreciate if someone can tell me how to do this. I don't want to spit the entry into two fields, as it slows the user down having to change cells, and this function has a heavy data entry requirement.
Thanks!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Hi,

If you expect the value entered, ie 2h or 3w to be used in your worksheet as 2 hours or 3 weeks, then the data ought to be stored in two different cells: one for the numeric value and one for the units. Make them BOTH Data > Validation: the former a WHOLE NUMBER and the latter a LIST.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Nope, that's not how they are used. They are used in calculation later only, to determine a rate (variable by resource type) a "time of day" multiplier, and a variable number of hours based on the type measure selected. As I mentioned in my first post, splitting these into two cells is NOT an option. Need to find a way to validate the numeric portion of the field entry... (This is heavy data entry program, and needs to be kept as fast as possible. The users want it this way, but I need to ensure they don't enter decimal values, i.e. 1.5d causes a big problem in the calculation, because a 1/2 day is treated differently than a full day. In that case, they would need 2 entries. 1 for full days, and one for 1/2 days.)
Thanks.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Nope, that's not how they are used.

then tell me HOW...

1.5d causes a big problem in the calculation

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Well, this is kind of proprietary information for my company... I don't think you need to know how we break down our work efforts to create a variable rate based on length of engagement. What I need is, a way to do data validation on a cell with right side containing a single character value, and left side containing some number of digits at a maximum length of 4. I just need a way to evaluate the characters to the left side of the last character, and determine if it's a whole number, or spit an error back to the user, and tell them to enter it as nnnnA.
Thanks


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Then please post in forum707 for a coded solution.

Be prepared to state your requirements in great detail, as to what should happen when the user enters invalid data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
I'm rather certain there is a solution in-cell using the Data Validation. There is an option for "Custom". I would have thought there would be some way to do this.... I would far prefer not to have this as a Macro, as a general rule.

BTW: We are using Office 2007, so Excel is a bit more robust in this area. Should have mentioned that earlier.
Can anyone help?
Thanks


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

On the sheet, you could do something like this, but it is ONLY a warning, and does not prevent the user from entering this particular piece of invalid data, like 2.5d...

1. In a MODULE, paste this function...

Function RemAlpha(strS As String)
':remove ALPHA from a string
     Dim re As Object ' object to hold Regular Expression object
    
     Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application
    
     With re
         .Global = True ' find all matches not just first
         .MultiLine = True ' over multiple lines
         .IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
         .Pattern = "[A-Z]" ' regular expression for numeric range
         RemAlpha = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
     End With
  
 End Function

2. On the SHEET, enter this formula, assuming that A1 is the cell to edit...
[tt]
=IF(VALUE(RemAlpha(A2))<>INT(RemAlpha(A2)),"YIKES","")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

try using this as a CUSTOM validation, assuming the cell is A@...
[tt]
=VALUE(LEFT(A2,LEN(A2)-1))=INT(VALUE(LEFT(A2,LEN(A2)-1)))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kristal9]

Just curious, how would you keep the user from simply entering a number into the field. (e.g. 2). Similarly, could they enter 24H could they expect the same results as if they were to enter 1D?

 

[SkipVought]

and THAT could be solved by creating a list like this and using a Named Range...
[tt]
UnitList
h
d
s
w
m
[/tt]
[tt]
=AND(VALUE(LEFT(A2,LEN(A2)-1))=INT(VALUE(LEFT(A2,LEN(A2)-1))),NOT(ISNA(MATCH(RIGHT(A2,1),UnitList,0))))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Thanks kristal9. I assume it was you, since you saved this thread in your archive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kristal9]

Indeed it was, saved into my "clever Skip solutions" folder. I

 

[Scott24x7]

Skip, that worked great. Thanks, JUST what I was after.
Actually 1d is NOT 24h. And entering 24h is fine, if they want to buy 24 hours of time. This is exactly why I need this differentiation. In reality, 1d is something less that 8h. (A "work day" being 8hours). I factor my resources differently. But that is precisely what I am after.
Cheers!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."