Conditional formatting formula character limits

[johnboston]

I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John

 

[johnboston]

#NAME?, thanks for the word "refedit". Thanks to each for the posts. I have to do some things but will post additional info and a clarification as to why this has become a conditional formatting matter, at least from my understanding. Thanks again for the help to this point. John

 

[johnboston]

I appreciate help to this point, and really am trying to be brief. I think it is helpful to explain how I got to the point where this has become a conditional formatting matter and in doing so suggestions on how to better address the matter may <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
become apparent to anyone with better understanding of Excel than myself. Suffice it to say, the project started out simple then little by little became more complex.<o></o>
<o></o>
Originally, the person preparing the schedule ("scheduler") for a 24 hour facility would type out the comprehensive schedule in Word. Sometimes, staff would find that the scheduler made errors in addition and they were either scheduled for more or less hours than they expected. <o></o>
<o></o>
I designed a straightforward spreadsheet ("Sheet1") that had rows for the staff member's name, and columns for

1) the date (under which the scheduler would type the scheduled shift (for example, 730-1600))<o></o>

2) the scheduler to enter the number of hours of the shift (for example, 730-1600 would require an entry of "8")<o></o>
<o></o>
Then at the end of the row there would be a cell which SUMmed the hours entry of each shift for each individual staff member, providing a "Yes" or "No" as to whether the tallied number matched the expected FTE of the staff member.<o></o>
<o></o>
Straightforward, no real problems, until ...<o></o>
<o></o>
The scheduler tired of manually entering the number of hours. To address this I added a formula to the "hours" column which would automatically enter, for example, "8", if the scheduler entered "730-1600". <o></o>
<o></o>
This eventually led to a dropdown list which included the most commonly entered shift entries ...<o></o>
<o></o>
730-1600, 730-2000, 1130-2400, 1530-2400, these were straightforward.

The entries to address overnights which began on one date and continued to the next date were slightly more complicated (and since they required a specific number of spaces to graphically lay out properly on the PRINTED page just seemed to add to the reasonableness of using a dropdown list). These <o></o>
<o></o>
entries were ...<o></o>
<o></o>
0800, 1930, 2330, 0800 1930 (This entry requires 10 spaces between "0800" and "1930" in order to work correctly), <o></o>
<o></o>
0800 2330 (This entry requires 10 spaces between "0800" and "2330" in order to work correctly), 0800 D <o></o>
<o></o>
(This entry requires 15 spaces between "0800" and "D" in order to work correctly),

Then entries for regularly scheduled days off D [scheduled day off], and Dr [requested day off, but not a vacation request].<o></o>
<o></o>
The sheet worked fine for it's intended purpose (i.e. automatically calculating the hours of an individual's schedule without forcing the scheduler to manually entering the hours of each shift).<o></o>
<o></o>
I hope this continues to make sense.<o></o>
<o></o>
The next design consideration occurred when the Scheduler had to make an entry other than the available options, for example "Vacation, 8 hours".<o></o>
<o></o>
How I handled this was for a pop up alert to open cueing the Scheduler to realize that they were making a "Special Entry" and that this entry would require a manual entry for hours.<o></o>
<o></o>
This all continued to work fine.<o></o>
<o></o>
By way of a number of considerations, the Scheduler decided to allow greater input from the staff on their own schedules, making available a so-called "self-scheduling" system.

To accommodate this, I designed a workbook of which "Sheet1" became the foundation. Another worksheet entitled "Requests" allows each staffmember to enter their own requested shifts/schedules. Using many of the same structure of "Sheet1" and this seems to work fine. People type their own requests on "Requests" and those entries then populate cells on "Sheet1". This <o></o>
<o></o>
saves the time of the Scheduler from doing all the typing and it gives staff a sense of greater input into designing their schedules.<o></o>
<o></o>
This is where conditional formatting has begun to become helpful.<o></o>
<o></o>
When a staff individual enters something other than a dropdown menu choice they receive an alert, "This appears to be a special entry. Please know that this entry will not be included in shift staffing or total hours counts. Thanks."<o></o>
<o></o>
The person may allow to keep that entry as it is and that entry is then populated onto the "Sheet1" from which the Scheduler will ultimately put together a schedule.<o></o>
<o></o>
By way of conditional formatting, the "hours" cell for an example entry of "Vacation, 8", will now show a "?" with a blue background.

This serves as a visual cue to the Scheduler that an entry is required in order for pay period hour tallies work properly.<o></o>
<o></o>
This all worked and the formula used was ...<o></o>
<o></o>
=IF(OR(K4="730-1600",K4="1530-2400",K4=800,K4="0800 2330",K4="0800 <o></o>
<o></o>
D"),8,(IF(OR(K4="730-2000",K4="1130-2400",K4="0800 <o></o>
<o></o>
1930"),12,(IF(OR(K4=1930),4,(IF(OR(K4=2330,K4="d",K4="dr"),0,"?")))))))<o></o>
<o></o>
What happened next required an addition to the conditional formatting of a second condition.<o></o>
<o></o>
Sometimes the Scheduler would look at the entry "Vacation, 8" and decide that was acceptable and would then manually enter an "8" in the column as prompted by the blue-backgrounded "?". Obviously, once the manual "8" was entered, the formula which had worked fine was now typed over and gone (i.e. not available to function). <o></o>
<o></o>
On later review of the schedule the Scheduler might then decide that the 8 hours of vacation was not a possibility and change the entry, for example, to "730-2000" and forget to change the hours from "8" to "12" (since the formula that would <o></o>
<o></o>
have normally automatically made the change was erased when the "8" was entered earlier). This would obviously throw off the tallied hour count for the individual of the pay period.<o></o>
<o></o>
Other times what would happen is the Scheduler would, after reviewing the schedule in full determine that that employee that had wanted vacation could now have it, but that employee made other plans; all the same the Scheduler decides to give the individual a day off, a "D". Again, if the Scheduler does not manually change the hours cell to "0", the hours count will indicate that the individual is scheduled for enough hours for the pay period, when in fact they are not ... they will come up short one shift; short because of the "D" which = 0 hours, but because the formula that would have automatically entered "0" had been <o></o>
<o></o>
previously typed over, the entry of "8" or "12" hours remains, throwing off the tally of hours.<o></o>
<o></o>
All that is background to the solution which was effectively addressed by a conditional statement that turned all cells with manual hour entries highlighted in green.<o></o>
<o></o>
Doing this allowed the Scheduler to visually look over Sheet1 and find any cells in green and make sure the number entered in that cell did in deed accurately reflect the number of hours associated with the schedule in the cell to the right.<o></o>
<o></o>
So I share all this because I think the formulas, as cumbersome as they are are working, they just are becoming too long as staff members come up with new configurations of requests which need to be added into the dropdown list of "acceptable" entries.<o></o>
<o></o>
I know this has been an awfully long post, and I apologize for that. I just don't know how to proceed with what I think I have as a pretty simple set of problems that would be solved by cumbersome and long function statements that simply have many references, so many that they take up too many characters in the refedit box.<o></o>
<o></o>
Having said all this is there a way for me to present my question here more clearly or in such a way that gives more insight to those of you with greater skills as to how to approach the problem.<o></o>
<o></o>
For me, the problem seems to be if the refedit box allowed more characters, it might work. That seems not be an option. Incidentally, is it right that actual functions/formula lengths may exceed the 255 limit?<o></o>
<o></o>
Thank you so much for any insight you might offer on this. I apologize if none of this is helpful, but I can't figure out how to present the problem without offering how it got to this point over a period of months. <o></o>
<o></o>
Thanks, again, for any suggestions. John.

 

[mole999]

ok the 255 limit, if you created a range and named it you can use the range name, so that can shorten the length of the text entry, and fit under the limit

I know you are using the drop downs to pick the shifts, and unless people are that proficient they take a period of time to enter, mouse pointer, down arrow, scroll your list, find the exact entry you want, click on it,

Most people are more proficient at typing


suggestion time

a column, used to accept variations, V for vacation etc, applied as necessary


many free timesheets around

http://spreadsheetpage.com/index.php/file/employee_time_sheet/

next column, hour / minute entry for shift start

next column, hour / minute entry for shift finish

these could be set up to ensure with code that , . ; all enter as : to ensure time works

with valid times, then beyond midnight can be automatically allowed for and you could have shift lengths that, aren't so restrictive

could be much quicker, and less confusing to maintain

 

[mole999]

oops, posted a link above, and realised it went in the wrong place, and the edit time expired, so it will read weird

 

[johnboston]

mole999, thanks for the response.

I know there was something valuable in your post in the suggestion of accepting variations, but, forgive me please, I simply didn't understand it. So if you can try re-explaining it, and don't be afraid of offending me with simplicity, (as you can probably tell I'm comfortable with IF statements and slight variations from there) I'd be appreciative.

I agree with you that people prefer typing, the problem I've run into is that they come up with a large number of variations for the same shift, which in turn, makes writing formulas aggravating, because the formulas become so long in an attempt to capture all the variations (hence, that puts me back at the character length problem I'm fixated on, but I have accepted there has to be a different way to accomplish what I'm trying to do). As an example "730-1600" may be entered as 730-1600, 730a-4p, 730AM-4PM, 7:30-4:00, 7:30A-4:30p, and on, and on, and on, and on.

Additionally, eventually what is generated is the official timesheet for the unit which then goes to another department (it's a government office) for further processing.

Payroll requires that the sheet presented to them contains certain data.

So, what happens is the printed version of the spreadsheet is designed to mimick the appearance of a document that used to be handtyped.

I know I'm wordy, and apologize for that, but really do appreciate your time spent helping me with this. John.

 

[johnboston]

Thanks for the link. I think i may have poorly explained that the spreadsheet eventually generates a one sheet schedule with the weekly schedule for about 30 employees, so if I've used the term "timesheet" i was in error ... this spreadsheet really generates a schedule for a group of individuals (from which I suppose individual timesheets might be generated). Thanks, John.

 

[mole999]

if you limit the input to only time values, that will solve some of the problems,


"730-1600" may be entered as 730-1600, 730a-4p, 730AM-4PM, 7:30-4:00, 7:30A-4:30p,


the output can be crafted to the payroll departments exact specifications, they do no need to see the input. (an if staff want 4pm, then the format can show that, its still stored how excel wants it to be though)

its possible using values boxes to provide a start and finish time in 15 minute increments, the art is to be adaptable to any needed change which stops you having to re create the file.

you could have day sheets for all staff, or a sheet for each staff member, the aim being to do as little work, and no maintenance at all

at the end of the week, a further macro could read the books in one location and create a master for payroll

 

[johnboston]

Mole999,

Thank you for the suggestion. I will have to learn how to limit the entries to time values (hopefully it is no big deal), and I can try to figure that out on my own, or if you have time, I don't mind your instruction. I was really tired last night by the time I finished that lengthy post and thought maybe by now examples might be of some use (?). I hope. Thanks again for any help you might offer.

As examples ...
Cell D4 is where the shift is entered.
Shift options, at present,
0730-1600
0730-2000
1130-2400
1530-2400
2330 0800 [there are 10 spaces to accomodate wraparound text and the desired appearance on the printed page]
1930 0800
1930
2330
D [a regularly scheduled day off]
Dr [a requested scheduled day off without using vacation]

There are other options I'd like to add but I think I'm constrained by nesting limitations and formula length limitations.

Cell D5 is where length of hours of the shift is tracked. For example, if the entry in D4 is "730-1600", then D5 automatically fills with "8" (an eight hour shift). This pattern is repeated throughout the 14 day pay period and then each of these daily entries is added to a tally elsewhere in the sheet to add to a total number of scheduled hours for the individual for the pay period.

To this point this formula has worked fine ...
=IF(OR(D4="730-1600",D4="1530-2400",D4="800",D4="0800 2330",D4="0800
D"),8,(IF(OR(D4="730-2000",D4="1130-2400",D4="0800
1930"),12,(IF(OR(D4=1930),4,(IF(OR(D4=2330,D4="d",D4="dr"),0,"?")))))))

Conditional formatting ...

If D5 equals "?", based on the formula noted, the background of D5 turns blue. This is simply a visual prompt for the scheduler that D5 is waiting for a manual entry. A question mark is generated if anything other than the listed options is entered in D4. This conditional format has not been a problem.

Second conditional format is where I've had difficulty (I think because of refedit character length limitations).

What I want is for D5 to turn green if value in D5 is a manual entry. If needed I can explain why, here in this post, I want this, but did explain it in an earlier very long post. [There was one ERROR in that explanation - the line that read "Doing this allowed the Scheduler to visually look over Sheet1 and find any cells in green and make sure the number entered in that cell did in deed accurately reflect the number of hours associated with the schedule in the cell to the right."

CORRECTION
the words of that sentence corrected read "Doing this allowed the Scheduler to visually look over Sheet1 and find any cells
in green and make sure the number entered in that cell did in deed accurately reflect the number of hours associated with the
schedule in the cell to the LEFT (correction)."

The most effective way I could think of to accomplish the what and why of this (the turning green of D5) was to enter a second condition which is
"Cell Value is"
"not equal to"
"=IF(OR(D4="730-1600",D4="1530-2400",D4=800,D4="0800 2330",D4="0800
D"),8,(IF(OR(D4="730-2000",D4="1130-2400",D4="0800
1930"),12,(IF(OR(D4=1930),4,(IF(OR(D4=2330,D4="d",D4="dr"),0,"?")))))))

This worked fine until I tried to accomodate more options of possible entries, which pressed me against the refedit box
character limits ... which prompted me to write. I hope all this makes sense.

A variation of the solution offered will more likely than not have application to cells E4, F4, G4, H4, I4, J4 which are used, based on the entry of D4, to track staffing for various shifts or shift parts.

It is really pretty cool when it works ... and it has worked until the apparent desire for more shift entry options.

Thanks for all the considerations, help, suggestions, thoughts, advice ...

John.

 

[johnboston]

If I limit the input to time values only, would individuals be able to type in, for instance, "military leave, 8"? I'm really not trying to be impossible. These are the things I'm running into. One problem at a time, though I guess, right? Thanks, John.

 

[mole999]

cunning plan (as a test), adapt to suit, but try on a blank workbook

I used M2 as the starting point

730-1600 8
1530-2400 8
800 8
0800 2330 8
0800 D 8
730-2000 12
1130-2400 12
0800 1930 12
1930 4
2330 0
d 0
dr 0

your value on the left, the intended result on the right in N

in D5

from the menu Data > Validation (this is 2003)

List, > allow > source =$M$2:$M$13

which gives you a drop down box limited to you list

OK

in E5

=VLOOKUP(D5,M2:N13,2,0)

which looks at the value in your drop down, and returns the time range you set

its rough, and with no value currently returns an NA#

but a start maybe you can adapt

give it a try

not an IF in sight