Data Bar - You cannot use relative references

[tobermory]

I'm trying to replicate the colour bars in table two, in table one. It's important for the presentation I'm doing I use numbers, not percentages. Basically the data bar represents how the proportion of how many people responded to answer in their group.

Table two is easy enough, just just zero as minimum, 1 as maximum.

The first table, requires a small formula, a sum of the total responses in the group. I can do this, however it wont permit relative references. I get a 'You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon set' message.

File can be downloaded here - https://easyupload.io/tfygrp

Is there a cheap trick around this? I'm looking to around 20 tables in this format, having to manually formula every one would take forever.

 

[pjmorris]

I managed to do it by formating each column top cell and using the formula =sum($B$2:$D$2) to obtain the maximum. I then copied the three cells, highlighted the remainder of the table and pasted formats. Excel then adjusted the formula as required. It does lead to a lot of conditional formats and amending them might be quite tedious (or delete the lot and start again!). This was my finished table:

Book1
	A	B	C	D
1	Group	Yes	Maybe	No
2	Group 1	27	20	3
3	Group 2	10	11	16
4	Group 3	13	1	4
5	Group 4	27	6	28
6	Group 5	30	22	8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D6	Other Type	DataBar		NO
B3:B6	Other Type	DataBar		NO
C3:C6	Other Type	DataBar		NO
D2	Other Type	DataBar		NO
B2	Other Type	DataBar		NO
C2	Other Type	DataBar		NO

HTH

 

[GraH]

Hi,

I was thinking one can maybe "fool" CF.

First add your formula in a named range (relative reference)

Then apply the CF using this name in the formula

At first glance it works, but looking closer the relative portion is off. The CF in blue is based on the portion of each answers to the total. You don't have the same visual at the left.

Sorry, can't do better then @pjmorris.

 

[pjmorris]

Hi GraH, I think your approach does work. I defined =RowSum just as you did yours and got the following visual:

I think yours only appears wrong as you have 100 in Group 1 - No, rather than 3 in the OPs example.

Or am I missing something?

For info, this is the dialogue when I setup the red colour having started by selecting the entire column:

HTH

 

[GraH]

Hi Peter,

I used some random values for testing purposes. And I did it like you explained.

Maybe like this it's more clear why I still think it is wrong.

In G-I range, the relative proportion is right. Somehow CF is considering the values in the other (previous?) rows. As you can imagine, I was not expecting this. So I'm missing the clue here.

 

[pjmorris]

This is really strange. I've done a test and completely agree with you. The only difference between the two tables below are the numbers in the top row:

and

There ought not be any difference between the two rows and certainly shouldn't be a change in Group2 when Group1 is changed.

Most odd.

 

[Fluff]

It appears that everything is based on the sum of the 1st row B2:D2, whilst the defined name by be relative CF seems to be treating it as absolute, possibly because you can't use relative references.

 

[GraH]

It appears that everything is based on the sum of the 1st row B2:D2, whilst the defined name by be relative CF seems to be treating it as absolute, possibly because you can't use relative references.

View attachment 34368

That actually makes a lot of sense the way you see it @Fluff.