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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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?
 

blujein

Thread Starter
Joined
May 12, 2008
Messages
158
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?
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

blujein

Thread Starter
Joined
May 12, 2008
Messages
158
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.
When I enter the formula you posted above, it reports: "The formula you typed contains an error."

Am I maybe doing something wrong?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

Attachments

blujein

Thread Starter
Joined
May 12, 2008
Messages
158
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.
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?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

Attachments

blujein

Thread Starter
Joined
May 12, 2008
Messages
158
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.
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

blujein

Thread Starter
Joined
May 12, 2008
Messages
158
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.
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).
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
PS The world is small when it comes down to electronic communication.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top