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

 

[mole999]

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.

what i was trying to get you to move towards with the two time enteries could have had

ML 08:00 16:00 8:00, in four columns

but the list i have just demonstrated, can be extended for those options, you'll just have to best guess most possible answers

have a look over the last several months of variations to see what crops up, common options to the top of the list, also ask those that use it, what should be there for them to be accurate

 

[johnboston]

mole999,

this is cool! i'm going to chew on this for awhile and see if it doesn't address some of my questions.

What does the "0" in "D5,M2:N13,2,0"? I've always shied away from VLOOKUP, but this is great introduction for me. I've never used it before. I gather the "D5" is the cell I'm working in, the "M2:N13" is the reference/list range, the "2" must represent the column next to M, I think, is that right, in this case N?

Thanks. John.

 

[johnboston]

what i was trying to get you to move towards with the two time enteries could have had

ML 08:00 16:00 8:00, in four columns

but the list i have just demonstrated, can be extended for those options, you'll just have to best guess most possible answers

have a look over the last several months of variations to see what crops up, common options to the top of the list, also ask those that use it, what should be there for them to be accurate

mole999, just saw this ... I have tried this, but suddenly when we went from manual handwritten to using a computer for individuals to enter their own data all kinds of new variations started popping up, partly, I think, because some people want to confound the spreadsheet in an effort to retain handwritten sheets ... I could be wrong, these are all great suggestions ... thank you, again, I'm going to play with the vlookup for awhile. Will probably have more questions. This may give rise to new ones. Thanks. John.

 

[mole999]

mole999,

this is cool! i'm going to chew on this for awhile and see if it doesn't address some of my questions.

What does the "0" in "D5,M2:N13,2,0"? I've always shied away from VLOOKUP, but this is great introduction for me. I've never used it before. I gather the "D5" is the cell I'm working in, the "M2:N13" is the reference/list range, the "2" must represent the column next to M, I think, is that right, in this case N?

Thanks. John.

The 0, ensures an exact match, 1 could pick the closest, but that could just confound the task you have.

the intention would be to create your lookup values on a hidden page, and name them as a range

so you might call all the selected values as "MYREFERENCE", and then the vlookup would be =VLOOKUP(D5,MYREFERENCE,2,0) which works very well, i was thinking that in the references

1530-2400 8 15:30-00:00, would be found by =VLOOKUP(D5,MYREFERENCE,3,0), which is eleven characters, and easy to utilise for a accounts time sheet, and the times can be fully extracted

 

[johnboston]

mole999, this is working well, thanks! I just read this latest post. Two questions ...

I tried, before reading your post, to set up a "References" sheet in a separate worksheet within the workbook but excel gave me a message stating I couldn't refer to other worksheet ranges within the workbook, maybe I entered it incorrectly ... a suggestion would be appreciated.

I instead made the range several hundred rows below the main data (which will result in a bunch of sheets of paper being printed if someone just hits "print".) It's working fine in practice, though.

Second question

I'm foreseeing a problem when it comes time to print the actual schedules that get entered in the schedule book. Here's what's happening.

The sheets that get printed and posted are called Week 1 and Week 2. All the data for these sheets, ultimately, is pulled from Sheet1 (the master sheet) on which the scheduler makes changes.

VLOOKUP is new to me and I'm having fun using it, but I'm wondering if there is a way to take the variety of entries that appear on Sheet1, which has pulled data from the Requests sheet (where people manually enter shift requests, or choose from a now greatly expanded choices of shift possibilities in a dropdown menu), and convert all the, for example, variations of day shift requests to one format ...

730a-430p
730a-4p
7:30-4:00
7:30-16:00
etc.

take all of those from Sheet1 and automatically plug/convert them on Week 1 and Week 2 to "730-1600"? That way when the actual posted schedule prints all the shifts are in the same format?

Thanks for all your help.

Continuing to plug away at updating the workbook and having fun learning new stuff.

John

 

[mole999]

Try This

Name a Sheet as Lookups

on Lookups create your list


730a-430p
730a-4p
7:30-4:00
7:30-16:00

add your times to the right

8
8
8
8

to the right of them

0730-16:30
0730-16:00
0730-16:00
0730-16:00

select the three columns and all the rows with data

Insert > Name > Define

Name it and makes sure the reference covers your values

maybe MYRANGE,

dosent really matter as long as the word is not defined for excel use, Time, Date, type words can easily cause problems

then on your data entry page

=IF(ISERROR(VLOOKUP(D5,MYRANGE,2,0)),"",(VLOOKUP(D5,MYRANGE,2,0)))

this layout removes the error markers that can display like NA#

lets get this bit right before expanding any further

 

[johnboston]

mole999, this is all very cool, thank you! I knew there was an "IF" statement in there somewhere ; ) i didn't know the "iserror" function. I kept trying "<>" and got odd results. This is going to take me awhile. I'm writing down things I need to update on the sheet(s) and questions I have. I think, in some ways, this gets me around the original conditional formatting questions ... and brings with it new ones. This is very fun and great learning ... I think I'm at the point, though, where I'm obsessing ... I lay down to sleep and think of things I need to update or a different way to approach a question ... and a paraphrase of the phrase on your posts about *knowing there's a better way, but just not knowing them*. Thanks, again for all the help. John.

 

[johnboston]

mole999, thank you for all your help on this! During a dry run it worked very well. I learned a lot this weekend and appreciate your patience. That VLOOKUP is great ... using it addressed a number of the concerns I was trying to workaround with conditional formatting.

The ISERROR function worked well, but because of the way I have things set up I couldn't figure out a way to use it without creating a lot of probable frustration for the person who will be making changes to the requests of individuals. I'm sure it is a design flaw in the way I set things up. I could explain it but it would be cumbersome to explain. All the same, if you're interested I wouldn't mind explaining it. I can tell there are times this function is going to be useful.

This may seem obvious to you, but I've done a lot of typing/entering on this this weekend and getting introduced to and more comfortable with VLOOKUP was a big step - use of ISERROR may be obvious in this, but I haven't entirely figured out the function ... is there a way to return a "?" instead of the error values? I know the formula you offered me ...

=IF(ISERROR(VLOOKUP(D5,MYRANGE,2,0)),"",(VLOOKUP(D5,MYRANGE,2,0)))

this layout removes the error markers that can display like NA#

...

worked great to remove the error markers, but it would be useful, based on other formulas I've set up, if the error markers showed up as a "?". (I tried using VLOOKUP for this, but I guess error markers are "special").

Thanks, again for all your help with this. You've helped me make great improvements to the functionality and user-friendliness of this tool.
John

 

[johnboston]

oops, think I may have figured out the "?" thing ... used your suggested formula with one change

=IF(ISERROR(VLOOKUP(D5,MYRANGE,2,0)),"?",(VLOOKUP(D5,MYRANGE,2,0)))

 

[johnboston]

mole999, as followup ... in dry runs the sheet has worked well. I did institute all your suggestions. It took a short while for me to appreciate them. Thanks again for your help on this. I'm going to ask another question as a separate thread, but because you've been so helpful I'll also write it here ... is there a way to password protect ten different rows (for example) with ten separate passwords? John.