Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Named Formulas and Conditional Formatting (Excel 2002 SP2)

    Attached is a scheduling calendar that uses named formulas to offer more options with conditional formatting.
    You'll notice that the calendar has weekends and (Canadian) statutory holidays shaded. But I also want to make it easier for the scheduler to see who she is scheduling. So if you change the instructor name, different blocks will be highlighted.
    So far, so good.

    But now I'd really appreciate another pair of eyes to take a look and see if there is a way I could define each instructors "available" time and make those definitions visible and updateable in the workbook. (Instead of buried in the named formula).

    The goal I've set myself is NOT to use VBA to solve this.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Formulas and Conditional Formatting (Excel 2002 SP2)

    Hi Catherine,

    I am not sure I understand what you are asking. Could you rephrase?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Sorry
    I was thinking that entering the formula directly into the Define Name dialogue box has a few drawbacks:
    1) The entry space is tiny and you can't expand it, using the arrow keys to move left and right within the formula causes funny cell references.
    2) When entering the formula, one has to be aware of the active cell in the spreadsheet, as the formula will change relatively.
    3) Unless one uses the Paste List command to paste a list of Range Names and their contents, I can see that updating and maintaining these formula could be a challenge.

    I was hoping to find a method for placing the appropriate formula into a cell, then referencing that cell with a range name. Of course, when I do that the range name uses the results of the formula instead of the formula itself.
    I was hoping to either:
    Find a function that will maintain the formula as a formula (not results) - or - a better strategy for creating range named formulas. For example - I'm starting to think that cramming a megaformula into that tiny dialogue box is not the best idea.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    1) If you hit <F2> the arrow keys can be used to move within the formula and not as the "pointer" for selecting cells

    2) Yes, this is a "strength" of the named formula. If you do not want a relative reference, you can "lock" the column and/or row by including a dollar sign ($) in front of the appropriate spot. But you seem to want the relative reference since you want to refer a relative position.

    I am still not sure what you are after, exactly.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    I think Catharine is looking for a way to specify the formulas for defined names in a worksheet instead of in the Insert | Name | Define dialog. Look at the Instructors sheet in the workbook she attached.
    Cell A13 contains a name ("AM") and cell B13 contains the formula this name refers to.
    If I understand Catharine correctly, she would like the definition of AM to change automatically if the text in B13 is edited.
    But she stated that her goal is to do this without using VBA, and I don't think that is possible.
    Using VBA, one could use the Worksheet_Change event of the Instructors worksheet to update the definition of a named range if a cell in column B is edited.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    I see that from her second post, but to me this is what she proposed as a way to solve a problem she has.

    I do not understand, what I think of as her "real question". The named formula is part of the issue, but "beyond" that. As you are well aware, there are many ways in excel (as in most things) to accomplish something. I was trying to understand what she is trying to accomplish and possibly propose an alternative method that is easier to setup/maintain.

    This is the "question" I am unsure about...

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    I admit that I do not understand exactly what you are after, so forgive me if my suggestion is completely "off-target".

    It appears to me that you want to be able to change the AM/PM/Eventing named formulas easier.

    Why not, instead of changing the formula, use some sort of lookup table (or tables) to get the value it should be reading. You could have a table with a column of names, then 3 Tables (named AM/PM/Evening) each with columns for Monday, Tues, etc. Then have the named formula use an MATCh (for the name), INDEX (for the Weekday) and Indirect with the column Heading in row 2 of Schedule (AM/PN/Evening) to define the table to lookup the value in.

    You will not have to edit the named formula, just change the values in the Table. If there is a pattern to it, using the first person as a start, you may be able to do it with a simple 2 way table and not involve the individual names or even fill the "larger table" based on a smaller table.

    Of course, If there really is an overriding pattern, you could have it all based on a smaller table or even just having one value.

    I hope this is clear, as I said, I am not clear exaclty on the "problem"...

    Steve

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Catharine,
    I don't know how practical this is, but if you enter the formula as something like:
    "CHOOSE(MATCH(SelectedInstructor,Instructors),FALS E,(WEEKDAY(Schedule!U"&row()&",2))=2,FALSE,FALSE,F ALSE,FALSE,(WEEKDAY(Schedule!U"&row()&",2))<5,FALS E)"

    and then define AM as =EVALUATE(EVALUATE(Instructors!$B$13))

    I think that will work.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    I did not know about that F2 usage - thanks!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    I think you are on the right track - I must go and rethink my strategy here.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Hmmm I will try this!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Thanks Steve, for reminding me of the forest and Rory for reminding me of Evaluate.
    Attached is a close to final version - mostly I'm going to tidy this up. But I've left the sheets "Scratch" and "Instructors Original" for anyone who wanted to follow my thought processes.

    You'll now see an Instructors sheet where it is easier to enter the schedule for each instructor. Formatting is updated on the calendar when a different instructor is chosen.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Hi Catherine,

    I think that if you start using my Name Manager, most of these drawbacks are handled nicely.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Formulas and Conditional Formatting (Excel 2002 SP2)

    Here is a way I thought of doing it which should be easier to maintain. I started with your original. I created a named range "Schedule" (=Instructors!$A$3:$D$10) and in the cells for each instructor and shift put the days to mark (12 = MT, 1234= MTWR, etc). The could be entered separated by spaces or commas if desired, but just having them numbers also works.

    Then I redefined your AM, PM, Evening. Each gets the weekday from the appropriate column, and tries to find it in the "VLOOKUP" of the selected instructor in the Schedule range. If it is found it is colored.
    From Schedule!B1, AM is:
    =ISNUMBER(FIND(WEEKDAY(Schedule!A3,2),VLOOKUP(Sele ctedInstructor,Schedule,2,0)))

    From Schedule!C1, PM is:
    =ISNUMBER(FIND(WEEKDAY(Schedule!A3,2),VLOOKUP(Sele ctedInstructor,Schedule,3,0)))

    From Schedule!D1, Evening is:
    =ISNUMBER(FIND(WEEKDAY(Schedule!A3,2),VLOOKUP(Sele ctedInstructor,Schedule,4,0)))

    With just these changes, you do not have to edit the named range to change what days are colored for a person, you just need to edit the weekday numbers in the appropriate cell in "Schedule"

    Steve

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Formulas and Conditional Formatting (Exc

    Thanks, I'll give it a try.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •