+ Reply to Thread
Results 1 to 14 of 14

Flip Rows and Columns and Conditional Formatting - Please Help

  1. 10-08-2013, 03:34 PM #1
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Flip Rows and Columns and Conditional Formatting - Please Help

    I have this lovely spreadsheet all completed and was just told the boss wants the columns 4-87 now as ROWS with the techs in the Column B.
    Can someone please help me with this?
    Next questions will be about conditional formatting - never done it and neither has she - but I was told I need to have the dates turn RED after 24 months past. GREEN when date is entered and also when less than 24 months. White/Clear for no data in cell.

    Thank you so much in advance for any and all help. Test Worksheet.xls
    Reply With QuoteRegister To Reply
  2. 10-08-2013, 03:52 PM #2
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    ooohhhh....I just discovered the Copy, Paste as Transpose feature - so now I just need help with the conditional formatting please...
    Reply With QuoteRegister To Reply
  3. 10-08-2013, 04:04 PM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    See the attached file.

    I used the macro below to get the result.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 10-08-2013, 04:50 PM #4
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    Tech Service Staff Training Completed 9-24-13.xlsxHere is the new spreadsheet with the columns and rows the way they want it - now I just need help with conditional formatting.
    Blue for new dates entered and dates less than 24 months
    Red for dates over 24 months
    White for no data in cell.
    Reply With QuoteRegister To Reply
  5. 10-08-2013, 04:52 PM #5
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Conditional Formatting Help Please

    I have no clue about this and please need help with conditional formatting.
    Blue for new dates entered and dates less than 24 months
    Red for dates over 24 months
    White for no data in cell.

    The date should be the current date (???)

    Tech Service Staff Training Completed 9-24-13.xlsx
    Reply With QuoteRegister To Reply
  6. 10-08-2013, 05:05 PM #6
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    CF formula for red cells:
    Please Login or Register  to view this content.
    for blue cells:
    Please Login or Register  to view this content.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  7. 10-08-2013, 05:23 PM #7
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Conditional Formatting Help Please

    Select your range of data, choose Condtional Format -> Formula

    Red Fill
    =IF(AND(C4>0,C4<DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY()))),TRUE,FALSE)

    Blue Fill
    =IF($A$1:$CG$20>=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())),TRUE,FALSE)
    Last edited by daffodil11; 10-08-2013 at 05:42 PM. Reason: holy typos batman
    Reply With QuoteRegister To Reply
  8. 10-08-2013, 05:31 PM #8
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Conditional Formatting Help Please

    @ daffodil11

    CF formulas don't need the if statement
    The result of a CF formula is always TRUE or FALSE

    =AND(C4>0,C4<DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())))
    Reply With QuoteRegister To Reply
  9. 10-08-2013, 05:39 PM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    Duplicate threads merged.

    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  10. 10-08-2013, 05:42 PM #10
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    I tried a bajillion times to do it without the IF statement and it kept putting silly quotes around my formula.

    $A$1:$CG$20>=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())

    What am I doing wrong? If I delete them, they just reappaear!

    CF Quotes.png
    Last edited by daffodil11; 10-08-2013 at 05:47 PM. Reason: pictures
    Reply With QuoteRegister To Reply
  11. 10-08-2013, 05:46 PM #11
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    =and(c4>0,c4>=date(year(today()),month(today())-24,day(today())))
    Reply With QuoteRegister To Reply
  12. 10-08-2013, 05:49 PM #12
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    Holy crap. So for Blue Fill, without the AND statement there's no need to put both sides of the equation into the formula.

    >=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())
    Reply With QuoteRegister To Reply
  13. 10-09-2013, 08:03 AM #13
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    I realized my post was in the wrong section - did not see a way to delete it since I resolved my first question. Which is why I reposted in the correct section for my formatting question.
    Reply With QuoteRegister To Reply
  14. 10-09-2013, 08:54 AM #14
    tracy702
    tracy702 is offline
    Registered User
    Join Date
    05-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Flip Rows and Columns and Conditional Formatting - Please Help

    I tried this formula


    >=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())

    and it isn't working - was I supposed to add this part to the end of the first formula?
    Sorry - I don't understand so I am a bit confused. Wish I new more about this stuff. lol
    Thank you both for all your help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formatting for multiple rows and columns
    By jimboryan in forum Excel General
    Replies: 2
    Last Post: 04-17-2013, 01:38 PM
  2. Conditional Formatting - Multiple rows/columns
    By Phillydog in forum Excel General
    Replies: 4
    Last Post: 03-21-2011, 04:50 PM
  3. How to flip rows & columns?
    By swee'pea in forum Excel General
    Replies: 2
    Last Post: 02-18-2010, 04:05 PM
  4. Conditional formatting to colour rows/columns
    By Bekanator in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-11-2006, 06:24 PM
  5. Images inside . . I need to Flip Columns, to Rows & Visa Versa?? SIMPLE RIGHT? :(
    By onlythebest in forum Excel General
    Replies: 5
    Last Post: 07-14-2006, 04:03 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1