Conditional Formatting and Relative References?

bradleykus

New Member
Joined
Apr 16, 2019
Messages
2
Hello,
I am no Excel master to any degree so I may be overlooking a really clear and obvious answer, but I'm trying to solve the following problem:

Basically all I'm doing is tracking a set of videos on a particular YouTube channel and what their projected viewcounts will be in the future. I then want the last video at the bottom of the list to have a data bar that tracks its progress to see how quickly/slowly it is catching up to the first video on the list (just chose the first one for demonstration).

As you can see in this first photo, it works fine; the conditional formatting shows the data bar in cell C14 properly filled up about a fifth of the way, showing that that video's 1,011,901 views are about a fifth of the top video's 4,741,156 views.

First photo: https://www.dropbox.com/s/9dp6vjkx296ien6/Excel 1.png?dl=0

Here is the rule set for that conditional formatting:

Second photo: https://www.dropbox.com/s/4mry0hz67qhn9dc/Excel 2.png?dl=0


Now, however, I want it to copy over for the rest of the spreadsheet, so that it is a running visual tracker as the viewcounts go up. I used the format painter to copy it over for the whole row, as you can see in the first photo above. What I then get is this:

Third Photo: https://www.dropbox.com/s/200ys8bect7jas2/Excel 3.png?dl=0

The data bar exists across the row in every cell (so that part's successful), but it is still referencing the first cell I chose (Cell C2) instead of the top cell in that column. That 3,668,361 in Cell AFI14 should only be about half filled when compared to the top cell in that column, 7,526,668. Here is what the conditional formatting is set to when I click on that cell.

Fourth Photo: https://www.dropbox.com/s/pvnpo8hqtipg68z/Excel 4.png?dl=0


Again, I know what it is doing - it's stacking up each cell against the original cell (C2), instead of using the new column's top cell each time. I thought using relative references would work but I guess conditional formatting doesn't allow the use of relative references - is there a workaround to this? Thank you!
 

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
136
Try changing your reference cell from $C$2 (always cell C2) to C$2 (always row 2, first column is C, others will adjust).

When creating a CF formula in Excel, you must design the formula for the first cell in the range, but use fixed/partially fixed references in such a way that when you copy the formula (or simply apply it to other cells in the range), it still works in all the other cells.

To fix a reference absolutely: $C$2
To fix the column, but allow the row to change: $C2
To fix the row, but allow the column to change: C$2

Good luck!
 
Last edited:

bradleykus

New Member
Joined
Apr 16, 2019
Messages
2
Unfortunately it just says the following:
"You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

Which I'm actually surprised by, but yeah unfortunately removing any one of the dollar signs makes it a relative reference instead of absolute.
 

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
136
Hmm, I suppose I have never tried to produce a custom CF using data bars etc. How annoying. :banghead:

You could of course do each one individually... :eek:

Would Sparklines in the row below be of any use?

Or take your bottom value, divide it by the top value and do Data bars for those? You could show the bar only and set the Maximum value to 1.

Just a couple of suggestions...
 

Forum statistics

Threads
1,082,323
Messages
5,364,580
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top