Results 1 to 5 of 5
  1. #1
    cwalker
    Guest

    Conditional Format (97 SR-2)

    Hi,

    I am in the process of creating a budget projection spreadsheet in Excel. The spreadsheet incorporates budget data from the past two years, and then year-to-date data for this year. I have it set up with the following formula:

    =IF(('2001 - General Fund'!J5)>0,'2001 - General Fund'!J5,('2-Year Avg'!Y5*'2001 - General Fund'!$N5)+I5)

    Basically, I want it to take the data from this year if it exists, and put it in the cell. If data does not yet exist for this year (i.e. September month-end figures), I want it to take the average percentage of budget from the past two years and multiply that by this year's budget, adding the previous month's number to that to give a fairly accurate projection.

    I have *that* part working fine. The part that's confusing me is a formatting issue that seems like it should be much simpler than it is. I want to try and distinguish between *actual* figures and *projected* figures through some type of formatting, be it bold, italics, color, shaded... whatever it takes. Unfortunately, I'm stuck with Office 97, and the chances of ever seeing a version here at work where it's probably easier to do this (like XP, where I'm sure you can probably just highlight that particular part of the formula and just change the text color) are slim to none.

    If, however, you know of a way to complete this daunting task in Excel 97, I would be much appreciative.

    Thanks!
    -Colin

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional Format (97 SR-2)

    Colin

    One solution is:

    In conditional formating use the Formula Is and make one equal to one side of the If statement, and then Add another Formula Is for the other side.

    I have attached an example workbook.

    Let me know if you have any other questions.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional Format (97 SR-2)

    What happened to the attachment?

    I'll try again... Its only 14KB?!
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    cwalker
    Guest

    Re: Conditional Format (97 SR-2)

    I think I found what my problem is -- whenever I try to link in the Conditional Formatting formula box to the cell that I want it to look in, it gives me an error saying that it can't do conditional formatting using cells in other worksheets, tabs, etc. It has to, apparently, be on the particular sheet that the conditional formatting is on.

    Any idea whether this might not be the case in newer versions of Excel?

    Thanks!
    -Colin

  5. #5
    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: Conditional Format (97 SR-2)

    Hi Colin,
    The same appears to be true in Excel 2000 but you can work around it by naming the range in question and then using that name in the Conditional formatting formula. For example, if you name Sheet1!A1 as 'TestRange', you can then add conditional formatting to A1 on Sheet2 using something like
    =TestRange>1
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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