Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format cells based on selection (Excel 2003)

    I'd like some advice from the Woody Gang on suggestions on how to format a table (of financial data) based on the value of a different cell. I have two requests that have to do with this same data but will write the 2nd one in a different post.

    The table has three rows and holds $ data for a 7 year period.
    <pre> Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7
    Data1 $12,000 $10,800 $9,720 $8,748 $7,873 $7,086 $6,377
    Data2 $12,000 $10,800 $9,720 $8,748 $7,873 $7,086 $6,377
    Data3 $24,000 $21,600 $19,440 $17,496 $15,746 $14,172 $12,755 </pre>

    I have another cell where they can choose a year range. This is either 3,4,5,6,7 years. I'd like to visually hide (or otherwise mark) the years which were not selected. Like this:

    if select 3, hide/gray out data for years 4,5,6,7
    if select 4, hide/gray out data for years 5,6,7
    if select 5, hide/gray out data for years 6,7
    if select 6, hide/gray out data for year 7
    if select 7, leave as is since this means the user wants to use all 7 years of data for the analysis

    else leave as is (white background, black text). Years 1,2,3 are always selected, they can't remove those years.

    I tried conditional formatting but I wasn't able to have the formatting overlapping. I know I can do this in VBA but would like to see if any of the formula wizards here can whip up something cool that would work too.

    Any ideas? I just want to make it visually obvious that the data in the years beyond what is selected, is excluded in some way. I'll write code or formulas later to only read the first 'n' years of data for summaries on other sheets.

    Thnx, Deb

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

    Re: format cells based on selection (Excel 2003)

    You can use conditional formatting with a formula as condition. Say that the cell containing the year range is C10, and that year 1 is in column B, etc. The formula would be

    =COLUMN()>$C$10+1

    See attached workbook. You can adapt it as needed/desired.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format cells based on selection (Excel 2003)

    Great solution and easy (now that I see what you did). I had tried conditional formatting but was triggering off of things like =Year>3 and selecting data for years 4,5,6,7, then do another conditional selecting cols for years 5,6,7 adn saying =Year>4, etc. but then I had multiple conditional formatting overlaying each other.

    Thanks alot... now I can ask my 2nd question for this same data set [img]/forums/images/smilies/smile.gif[/img]

    Deb

Posting Permissions

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