Data Bar - You cannot use relative references

tobermory

New Member
Joined
Jun 4, 2012
Messages
44
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.png


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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
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:

1615735749559.png


Book1
ABCD
1GroupYesMaybeNo
2Group 127203
3Group 2101116
4Group 31314
5Group 427628
6Group 530228
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D6Other TypeDataBarNO
B3:B6Other TypeDataBarNO
C3:C6Other TypeDataBarNO
D2Other TypeDataBarNO
B2Other TypeDataBarNO
C2Other TypeDataBarNO


HTH
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
959
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

I was thinking one can maybe "fool" CF.

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


Then apply the CF using this name in the formula
1615734377516.png


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.

1615736191143.png



Sorry, can't do better then @pjmorris.
 
Last edited:

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Hi GraH, I think your approach does work. I defined =RowSum just as you did yours and got the following visual:

1615737081657.png


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:

1615737200588.png


HTH
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
959
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
1615737549589.png


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

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
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:

1615745569859.png


and

1615745607924.png


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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.

1615746575746.png
 
Last edited:
Solution

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
959
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,060
Messages
5,704,037
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top