+ Reply to Thread
Results 1 to 17 of 17

Progress Bar (%) Hopefully quick and simple

  1. #1
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Exclamation Progress Bar (%) Hopefully quick and simple

    I am trying to create a progress bar which i am manually creating by simple formatting the following range of cells with a border - so basically displaying a rectangle with no fill

    d5:m5

    i have tick boxes on my spreadsheet - i have already set up the calculations in another cell so that when 7 out of 10 boxes are ticked - y2 will display 70% etc. that's all done.

    What i need now is to somehow tell my 10 cells to change colour according to the number in Y2.

    e.g. if Y2 says 70% i would like the first 7 cells of d5:m5 to change green - creating the illusion of a percentage bar filling up.

    is there an easy way to conditional format this or script this?

    or is their another easier way to do this///.



    thanks,

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Progress Bar (%) Hopefully quick and simple

    what happens for say 9%
    but for 10% increments
    try
    =COLUMNS($D$1:D1)/10<=$Y$2
    Last edited by martindwilson; 04-15-2014 at 08:08 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,520

    Re: Progress Bar (%) Hopefully quick and simple

    Highlight cells D5:M5, click on Conditional Formatting | New Rule | Use a Formula... , then enter this formula in the dialogue box:

    =(COLUMN(D$5)-3)/10>=$Y$2

    Then click the Format button, choose the Fill tab and select your colour. Then OK your way out, and Excel will automatically adjust the cell references to suit your highlighted cells.

    Hope this helps.

    Pete

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,576

    Re: Progress Bar (%) Hopefully quick and simple

    Attached is a solution using conditional formatting. (Next time it would be very helpful if you attached your file.)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Progress Bar (%) Hopefully quick and simple

    appreciate all replies. I will try them out shortly. thanks

  6. #6
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Progress Bar (%) Hopefully quick and simple

    When I enter the formulas shown above (both). it highlights the cells the colour I want.. but when I change y2 to ANYTHING they all just go "no fill". they only stay green when y2 is empty. I was expecting when y2 said 50% for half the cells to be green etc in horizontal way.

  7. #7
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Progress Bar (%) Hopefully quick and simple

    Could you not condition the first cell of the "percentage bar" so that when 10% is displayed this goes green. Then the first two cells of the "percentage bar" formatted so that when 20% is displayed they turn green, and then the first 3 cells so that when 30% is displayed they turn green etc, etc, etc.

    There would be 10 conditional formats total. Or have I missed part of your original query?

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,520

    Re: Progress Bar (%) Hopefully quick and simple

    @Muzza,

    Attach your workbook then we can see for ourselves what is going wrong.

    Pete

  9. #9
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Progress Bar (%) Hopefully quick and simple

    Muzza,

    Please see my attached file.

    In A1 type 1 or 2 or 3.

    If this helps then just apply this to the other cells, for 4, 5, 6, 7, 8, 9, 10 etc
    Attached Files Attached Files

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Progress Bar (%) Hopefully quick and simple

    you still havent answered what happens at 9% or even 11%

  11. #11
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Progress Bar (%) Hopefully quick and simple

    Quote Originally Posted by martindwilson View Post
    you still havent answered what happens at 9% or even 11%
    He said there are tick boxes which when ticked show 10%, 20% etc. So I assume what ever progress this is measuring, it is part of a 10 step process perhaps? Therefore no need for less than 10% increments. Although I could be wrong, obviously.

  12. #12
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Progress Bar (%) Hopefully quick and simple

    well this works
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Progress Bar (%) Hopefully quick and simple

    Sorry guys, been busy. Thanks for replies.

    The reason its a little trickier than simple conditional formatting is that not every tick box will be worth a standard percentage of the total.

    e.g. there maybe 10 tick boxes but that doesn't mean every tick box = 10%. the first tick box could be worth 30% with some others worth 5% etc eventually totalling 100%.

    plus I am creating multiple percentage bars - a couple for individual progress and a final bar for overall percentage completion

    I have attached how I eventually did it.... I think it works? hope this makes it more obvious what im trying to do.


    p.s. I don't think 1% or 11% etc will be necessary. But if I need it. I can just decrease the size of the cells and use 50 columns etc instead of the 20 or so I am now, as im using 1 cell = 5% currently. if that makes sense.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Progress Bar (%) Hopefully quick and simple

    the other one with multiple bars..
    Attached Files Attached Files

  15. #15
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Progress Bar (%) Hopefully quick and simple

    so why didn't you ask that in your original instead of feeding us info piecemeal?

  16. #16
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Progress Bar (%) Hopefully quick and simple

    I hadn't made that yet. calm down.

  17. #17
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Progress Bar (%) Hopefully quick and simple

    Quote Originally Posted by martindwilson View Post
    so why didn't you ask that in your original instead of feeding us info piecemeal?
    I stand corrected

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Progress bar (%) hopefully simple and quick
    By Muzza86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2014, 12:56 PM
  2. [SOLVED] How to separate text?
    By am1221 in forum Excel General
    Replies: 4
    Last Post: 10-10-2012, 04:56 AM
  3. Quick simple question!
    By Dermot81 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2006, 09:39 PM
  4. Quick help with a simple function!
    By bobbly_bob@hotmail.com in forum Excel General
    Replies: 4
    Last Post: 07-20-2006, 01:25 AM
  5. simple quick question
    By John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2005, 11:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1