How to keep line colors same when the sequence of line is changing

[Oleg_M]

Hello friends,

just a small help needed - imagine you have a chart with 7 lines, e.g. seven products sales over time. And you need to show sales in different channels...e.g. 3 channels. So we will have 3 same charts but with different data...products a ranked by sales, so for each chart product A might be on different place...that by default means product A will have different color at each chart - that is mess. Matching colors manually is another crazy thing.

What do you recommend? Any easy trick? Appreciate your help.

Best regards,
Oleg

 

[Chihiro]

I'd recommend uploading sample workbook. Without it we will be taking a guess at your data/workbook structure and solution provided may be off the mark.

 

[Hui]

Oleg

Firstly, Welcome to the Chandoo.org forums

The trick here is to be organised in your data reporting

If you have a large table with data, sumarise it as required in a report area
Keep the layout of each report area the same, so that Row 1 is always the same product regardless of summary type

 

[Oleg_M]

I'd recommend uploading sample workbook. Without it we will be taking a guess at your data/workbook structure and solution provided may be off the mark.

Hello, thanks for your reply -

Please find a sample workbook attached. Actually the question is how to have a specific color for specific customer (for example), when you may have a different rank of customer in different charts. Just to keep color-coding accros all data.

Regards,
Oleg

 

[Oleg_M]

Oleg

Firstly, Welcome to the Chandoo.org forums

The trick here is to be organised in your data reporting

If you have a large table with data, sumarise it as required in a report area
Keep the layout of each report area the same, so that Row 1 is always the same product regardless of summary type

Hi, thanks a lot for your welcome -
Actually the story is similar to what you are saying - we have a large table with data and building on it a several summary reports.
But when we are ranking similar items based on different facts (e.g., volume and revenue), they might have different ranks (e.g. Row 1 on volume, and Row 2 on revenue) and therefore will have different colors on charts.
I need somehow to marry color with concrete item.

Regards,
Oleg

 

[p45cal]

You could have a table somewhere with your Company name/Product name and the colour of each cell in that list being the colour you want that product's line to have in the charts. Two'tricks':
1. I've set up a little macro in the attached which will work if you have set up all your chart lines using a cell as that chart line's name:

(The chart line doesn't have to be named that way; if you've used a literal value when setting the chart up, as long as that name can be found on the sheet somewhere it should still work.)

What the macro does is go through all charts on the sheet, going through all the series on each chart, determining what that series' name is. If there is one, it looks on the same sheet for the first cell which contains that name, it then makes the line of that series the same colour as the font in that cell. In the attached it's the cells B4:B6 that it finds first for all the charts (except for Company 4, the first instance of which is at cell N7 so it uses that).

2. You could have a table somewhere with your Company name/Product name and the colour of each cell in that list being the colour you want that product's line to have in the charts. In the attached a macro searches for the company name in the list at cell X13 and uses the background colour as the line colour on the charts.

 

[Oleg_M]

You could have a table somewhere with your Company name/Product name and the colour of each cell in that list being the colour you want that product's line to have in the charts. Two'tricks':
1. I've set up a little macro in the attached which will work if you have set up all your chart lines using a cell as that chart line's name:
View attachment 46658
(The chart line doesn't have to be named that way; if you've used a literal value when setting the chart up, as long as that name can be found on the sheet somewhere it should still work.)

What the macro does is go through all charts on the sheet, going through all the series on each chart, determining what that series' name is. If there is one, it looks on the same sheet for the first cell which contains that name, it then makes the line of that series the same colour as the font in that cell. In the attached it's the cells B4:B6 that it finds first for all the charts (except for Company 4, the first instance of which is at cell N7 so it uses that).

2. You could have a table somewhere with your Company name/Product name and the colour of each cell in that list being the colour you want that product's line to have in the charts. In the attached a macro searches for the company name in the list at cell X13 and uses the background colour as the line colour on the charts.

Super, thanks a lot! We applied your macros a little bit updated - working perfectly!