Results for "challenge creating heat map data carries own unique values"

Good evening corriedtoppin,

Would a scale like this one be better for what you want to do?

Let me start over.

Good day excel world.

I have a concern.Below link have a list of countries that carries each country unique data.

Consumer Confidence - Countries - List

Now let's take the 1st two example:

#1 Country: Albania Data: -13.60
Below is Albania historical values 
snapshot:https://imgur.com/C3GjVvBIf 
you notice all there historical data are measure with a (-) number.
https://tradingeconomics.com/albania/consumer-confidence
Albania Consumer Confidence

and

#2 Country: Argentina Data: 42.40
Below is Argentina Historical values 
snapshot:https://imgur.com/ECIbh1j
If you notice all there historical data are measure with a positive number.
https://tradingeconomics.com/argentina/consumer-confidence
Argentina Consumer Confidence

My problem is this:

How can I use 3-Color Scale "Heat map", using conditional formatting, to show it correct color code for each country?

Below is the WRONG EXAMPLE of conditional formatting use

directly.

What do you suggest I do?

Hello Excel World.

I'm in the process of creating a 3-Color Scale "Heat map", using conditional formatting for investment purposes.

But I am having some challenges in creating the heat map, because each data carries there own unique values and may differ extremely from each other.

See Link below, of countries listings and the values outline under the header "LAST"

https://tradingeconomics.com/country-list/consumer-confidence

I also thought with myself. If the heat map should work effectively, each value or each cell reference should have there own heat map values.

But what is the effective way in creating these values or heat map ratio.

See link below of one of the example given.

https://tradingeconomics.com/albania/consumer-confidence

In conclusion.

What is the best direction to create this?

What are your experiences if any?

How do you want me to proceed?

Thanks in advance

corriedtoppin a écrit :

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

Hello,

That one look like a valid full name. If that's not working anymore, I can't help you.

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

I don't know. That's the name of the excel file: Countries Indicators #1 NSB (1)

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1).xlsx

End Sub

I am only getting and error.

corriedtoppin a écrit :

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

This is still not a valid filename...

The right syntax is simple : Complete Path + \ + Workbook name + Extension

I gave you an example : C:\User\FullPath\WorkbookName.xlsx

Your filename doesn't look like to this one !

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

Ohhh. I'm sorry going to try

You are just reading half part of my responses...

Pedro22 a écrit :

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

Your actual filename is not valid.

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Filename to adapt

End Sub

The above code

corriedtoppin a écrit :

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

Paste here the code you tested, please.

I'm not a soothsayer !

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

You can just test this code to be sure that the workbook is correctly opened :

Sub Test1()

    Workbooks.Open(Filename:="C:\User\FullPath\WorkbookName.xlsx") 'Filename to adapt

End Sub

Hello. I sorry for my manners. Thanks for your reply.

I did change the file name, "(Filename:="C:\User\FullPath\WorkbookBname.xlsx")" to

(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI").

and I made and error to state where I want to paste the values.

from:

.Range("B" & Rw) = WbSource.Sheets(Country).Range("F2")

.Range("C" & Rw) = WbSource.Sheets(Country).Range("G2")

To:

.Range("C" & Rw) = WbSource.Sheets(Country).Range("F2")

.Range("D" & Rw) = WbSource.Sheets(Country).Range("G2")

But somehow, it don't seems to work.

How can I get around this?

Thanks in advance

Hello (you may start a message with something like that...),

A possibility :

Sub ImportData()

Dim Rw As Long, RwMax As Long, WbSource As Workbook, Country As String

On Error Resume Next

Set WbSource = Workbooks.Open(Filename:="C:\User\FullPath\WorkbookBname.xlsx") 'Filename to adapt
With ThisWorkbook.Sheets(1)
    RwMax = .Range("A" & Rows.Count).End(xlUp).Row 'Last row
    For Rw = 1 To RwMax 'loop on rows
        Country = .Range("A" & Rw)
        .Range("B" & Rw) = WbSource.Sheets(Country).Range("F2")
        .Range("C" & Rw) = WbSource.Sheets(Country).Range("G2")
    Next
End With
WbSource.Close False
MsgBox "Work done !"

End Sub

Each Worksheet in Workbook B have to be named like the searched country in column 1 Workbook A.

I have a problem.

I want to extract data to create a list from workbook (B) into another workbook (A).

NB: Using the "=" sign is hard and long to extract data.

So my point is:

On workbook (A), I have a list of countries names from row A2 to row A51

Next. On workbook (B). Each country has it own "worksheet". From worksheet #1 to Worksheet #50 and all the values are in cells "F2 & G2" respectively.

I want excel to grab each worksheet values in "F2 & G2" in workbook (B) and paste it as a list in workbook (A) in worksheet (1).

How can I do that?

First proposal :

Good afternoon Jeremie,

Are you French? If so, why are you posting in the English section of this forum?

Anyway, can you share your file or a part of it, so that we can help you more easily?

Hi everybody ! I am pleased to be a new member of this group that has helped me so much trough many of my projects.

Here is my problem. I want to do the sum of values in a specific range of cells. In the picture provided, I would like to know the total value of "CAL JAUNE 20 X 50' SP". So I'd need to find all the cells that contain the value "CAL JAUNE 20 X 50' SP" in the B column and do the sum of all the cells in the same row but in the E colum. I'll have to do that with a bunch of other codes. Also the SUM of "CAL JAUNE 20 X 50' SP" has to be in cell A1 of sheet 2, the SUM of "CAL 100' HARM MOKA/6BTE" in cell B1 of sheet 2... The quantity of CAL JAUNE 20 X 50' SP is going to change as they are produce, so that's why I need to identify the cells with this value in it.

Thanks a lot in advance, J !:)