1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Creating Cell "Cold" And "Hot" Spots Based On Values In Microsoft Excel 2007

Discussion in 'Business Applications' started by blujein, Jun 16, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    I am hoping that someone can help me with what may be a complex question.

    I have a spreadsheet, similar to the demonstration file I have attached. In it, there are a range of columns. One set of columns (A - D), shows a list of numbers that are references to items in a store, and it also shows their location in the store, so it acts as a floor-plan. The next column (E) lists those items again to show the dollar amount that those items have produced in total sales (F).

    So, what I'd like to do is to add a conditional formatting to show me the "cold" and "hot" spots on the floor-plan so I can see where sales are most concentrated. I would assume that this would be done with a colour scale (using the range of values in column F to work itself out)?

    Either way, the figures in column F would change weekly, so the relevant cells in columns A - D would need to automatically change when they're matching cells in column E - F change.

    I hope this makes sense, as it's difficult to explain, but any help that someone could provide would be greatly appreciated.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    If I understand correctly you will update the $ in column F on a regular basis.

    You want the color of the equivalent number in E to light up in the block A-D, right?

    What is your criteira for "Hot" and "Cold" and how many varaints to dou want to see?
     
  3. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    Thank you for your reply, Keebellah!

    That is correct: I want the cell of the equivalent number in column E to light-up in columns A - D.

    The criteria is essentially the colour scale pre-set in the conditional formatting options for Microsoft Office Excel 2007 and above, which would be based off the lowest and highest dollar figures in column E.

    I hope that makes sense.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    It does make sense, since I don't have Office 2007 here I cannot look into the formats, but maybe you can do it yourself.
    What you need is the following:

    In cell G1 enther the following formula:
    Code:
    =RANK(F1;$F$1:$F$9)
    
    
    Then you copy the forluma down to row 9;

    You will het a ranking based upon the $ ammojtn in F

    Code:
    E       F                G
    ----------------------------------------------------
    9	$180.999,00	4
    22	$26.990,00	6
    98	$384.888,00	1
    132	$293.001,00	3
    145	$384.211,00	2
    176	$38.490,00	5
    284	$221,00     	9
    300	$2.293,00  	7
    854	$1.223,00  	8
    
    
    Now all you need to figure out is vlookup based upon the values in E and return the value in G to trigger the conditional format
    VBA code (macro) would do the job too, only then you need program it to be triggered on change.
    If you need help with this just post it.

    You've got something to work on for now.
     
  5. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    When I enter the formula you posted above, it reports: "The formula you typed contains an error."

    Am I maybe doing something wrong?
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    Well, have you looked up the syntax for RANK?
    What language are you using. If it's not English then you will have to lookup the equivalent function in your specific language:
    For Dutch its RANG,
    Once you type the = sign in the cell, you kan open the function list (fx) button in the address bar and then look for it there
    I have attached the 2003 version which can open, and the formula will translate itself if you have a different langguage version.

    You can always save this one as a 2007 version and it will work.
     

    Attached Files:

  7. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    That works for me - thank you for going to that effort!

    Could you also walk me through setting up the macro and whatever else I need to get the background colours of the cell range (A - D) dependant on their value?
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    No problem, will get back to you later today.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    Sorry about not getteing back sooner.
    Was deep into something else. I'll try to have it on paper and added to the sheet to show you, it's really quite simple but that's with everything, once you know how it works, it's simple.

    Bye for now.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    See attached version 2003 file.
    If you want to use it in 2007 you muast save it as 2007 with macros, the filename will then have the extension xlsm

    I think this is what you meant and wanted? :)

    Let me knwo.

    Bye.
     

    Attached Files:

  11. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    Thanks for getting back to me with that, mate.

    That's really close, but I'm a little confused with it.

    Essentially, the way the function will actually be used will be to monitor 300 items - not just the 9 that I had listed in my original demonstration file. Can you tell me what needs to change in the code to get that to work? Each item will have its own value, and will need to have a colour (perhaps a colour scale?) to represent it.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    This example can be used and all you neeed is increase the range to consider.
    How good are you with macro's?

    The vba code is all you need to edit.

    I can suggest the following oink to explain the colors and it's a good site to find examples
    http://www.cpearson.com/excel/colors.aspx

    I'll be glad to help but then I need more information.
     
  13. blujein

    blujein Thread Starter

    Joined:
    May 12, 2008
    Messages:
    158
    Thanks for the link, mate.

    I'm not great with macros, no ("not great" is an understatement).

    Also...in the workbook that I am working on, there are two sheets - one sheet shows the floor-plan (which I want to "light-up" using the colours), and the second sheet showing the item list, along with the ranks (that you provided the code for).
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    Two sheets is no problem.
    It's just a question of setting up the macro.
    If you feel like it, if you want, you can post a sample sheet like the one you'll be working with (no sensitive data if it's there) and I'll try and see if I can give you a basic working example that you can build on.
    Macro's aren't that hard, it's just a translation of what you want done to a code.
    I imagine it's the end of the day down under, it's 07:46 here and I'll be off to work in 20 minuts but I'll see the messages comming in anyway.
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    PS The world is small when it comes down to electronic communication.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/929647

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice