Conditional Formatting - Data Bars Based On Value From Another Cell Using INDIRECT

Apr 11, 2014

I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.

Basically I want to show a Data Bar in a certain cell based on the value from another cell.

It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.

As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.

For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.

So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.

If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.

Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?

View 3 Replies


ADVERTISEMENT

Data Bars Conditional Formatting

Mar 5, 2012

I am using the simple conditional format "Data Bar" to make a progress bar, going from 0-1 (0-100%), works fine .5 is halfway...ect. but what i would like it to do is to change to a different colour only when it reaches the 100% or 1 in this case so you can see quickly that this progress is complete.

View 7 Replies View Related

Apply Conditional Formatting To Bars In Bar Charts

Aug 29, 2006

I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem:

The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets.

Based upon the values in each of the cells that create and update the bars on the charts themselves:
I want the bars to be RED if the values are below the lower control limit.

And I want the bars to be GOLD if the values are above the upper control limit.

And I want the bars to be green if the values are in between the upper and lower control limits.

View 2 Replies View Related

Conditional Formatting Based On Cell Data?

Jan 29, 2014

I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.

In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.

The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.

I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.

View 7 Replies View Related

Conditional Formatting With INDIRECT And AND

Oct 22, 2007

I'm trying to conditionally format a cell based on the cells around it. There might be a better way to do this but this is what I have done. I'll just show some trials of formatting conditions I've done.

View 7 Replies View Related

Excel 2010 :: Conditional Formatting Using AND And INDIRECT

Jan 10, 2012

I'm trying to do conditional formatting that applies to an entire column (that way there are fewer conditional rules), but it only applies to a certain range of cells within that column. So what I did was setup the rule such as:

Applies To:

=$B:$Z

Formula I want to use:

=AND(MOD(ROW()-3,49)=38,INDIRECT("B"&ROW()-4)>=0)

If I copy the entire formula into a cell on the spreadsheet itself, it works just fine returning TRUE and FALSE depending on what row I'm on.

However, the above formatting rule does NOT format ANY row. But if I remove the AND function and the first condition (the one that limits which rows it's actually going to apply to) then it will work...OR if I do NOT use the INDIRECT function then it seems to work, it's some weird combonation of using the two together that is causing it to not evaluate to true.

Oh, and I thought of trying to get the formatting to work with the INDIRECT...then I put in the AND function but the only parameter was the original INDIRECT function (see below)...it stops formatting!

=AND(INDIRECT("B"&ROW()-4)>=0)

Office 2010 on Windows 7 64bit

View 5 Replies View Related

Macro For Conditional Formatting Using Isna, Match And Indirect In The Formula

Apr 2, 2009

I have configured conditional formatting to check valid entries in a dependant list. I used the solution provided by SHG at:

http://www.excelforum.com/excel-prog...ant-lists.html

Now I need to use a macro to activate and deactivate the conditional formatting due to performance issues.

When the formula for data validation includes the indirect funtion I got a compile error and the macro won't run.

View 3 Replies View Related

Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

View 3 Replies View Related

Conditional Formatting On Cell Based On Value

Jul 29, 2014

In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.

View 8 Replies View Related

Conditional Formatting Based On A Value In A Different Cell?

Apr 9, 2014

I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.

View 13 Replies View Related

Conditional Formatting Of A Row Based On A Value In A Cell Of That Row

Feb 17, 2010

I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.

the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.

View 12 Replies View Related

Conditional Formatting Based On Value Of Another Cell

Jun 4, 2012

I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:

A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"

However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.

View 7 Replies View Related

Conditional Formatting Based On % In Other Cell

Feb 15, 2013

I want to color format a cell based on a percentage value in another cell but I just can't get my head around how to solve it. I've attached a file that explains it better.

E.g.
The formatting for A1 should be something like:
If A2 is
> +/- 10% RED
+/- 6-10% YELLOW
+/- 5% GREEN

Attachment 214464

View 6 Replies View Related

Conditional Formatting - One Cell Based On The Value Of Another

Jan 6, 2014

I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.

View 4 Replies View Related

Conditional Formatting Based On Cell Value

Apr 17, 2014

I am manually formatting column J depending on what data is entered into column K. If the number entered into the cells in column K are equal to or less than what is in column J, I format the data in the cells in column J with the green you see above. If the data entered in the column K cells are greater than column J, I format it in the red shown above.

So, how can I have Excel do this for me, format column J cells depending upon what is entered into column K cells? The caveat here is that I also don't want any formatting to occur until data is entered into column K.

View 5 Replies View Related

Conditional Formatting Based On Value Contained In Another Cell

Apr 9, 2014

I have some problem with conditional formatting, basically what I'd like to do is color the cells, based on the value contained in one specific cell. I'm not able to make a formula that suits my job.

Basically I want to color code a cell if the value in a specified cell is between x and y.

I've attached a sample spreasheet: MrExcel.xlsx‎

View 1 Replies View Related

Conditional Formatting Based On Two Cell Results

May 6, 2009

I have a cell which I want to colour green if C6>9, yellow if O6>60 and red if BOTH C6 and O6 are above their respective numbers. The first two parts of this are obviously straight forward, but I cant work out how to get the third part of it to work.

View 2 Replies View Related

Conditional Formatting Based On Text Of Another Cell

Jul 30, 2009

I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed".

View 4 Replies View Related

Conditional Formatting Based On Two Cell Values?

Sep 21, 2013

I want to do conditional formatting based on two cell values.

i want to do conditional format the column 'R' based on clolumns 'J' and 'R'.
column 'J' is a text column named PRIORITY which can hold the values P1,P2,P3 an P4.
Column 'R' is nothing but difference of two columns 'M' and 'H'(both are time stamps).Column'R' is in below format.
=TEXT(M4-H4,"h:mm:ss").

column M and H are in below format:

eg: 9/11/13 9:41 AM

Now i want to format the column'R' like
if column J ="P2" and column 'R' > "00:10:00"(10 mins) =it should become red.
if column J="P3" and column 'R'>"00:30:00"(30 mins) = it should become red.

View 7 Replies View Related

Conditional Formatting Based Upon Format Of Another Cell?

Mar 25, 2010

Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?

View 8 Replies View Related

Conditional Formatting Based On Adjacent Cell?

Mar 4, 2012

I want the font of a value in a cell to turn green or red based on the value of the cell below it - NOT based on a set value or range like all of the other examples are based on. I want to apply a formula or condition to the entire table.

Column a has the date for each value, newest date is row 1 and oldest date is row 250. Column b-k has the number value for each animal species we are tracking. If there are more animals than the day before, the value went up and I want the font to be green. If there are less animals than the day before, then value went down and I want the font to be green.

There are about 250 rows in each table and I have 4 tables. I thought I could do conditional formating but I can only find how to change font color based on a set value. For instance, for each day there are over 10 turkeys, then it would be green. That is NOT what I want, see below:

Mon 5 no color change
Fri 5 color change to green
Thu 4 color change to red
Wed 5 color change to red
Tue 7 color change to green
Mon 5

date looks more like this - col a is date, col b is turkey, col c deer, etc

Mon 5 4 3 4 2
Fri 5 3 4 2 7
Wed 5 2 7 1 0
Tue 7 4 6 5 9
Mon 5 1 1 5 4

I would think this is similar to daily stock prices, if data goes up then it is in green, but if the price goes down then in red. Not based on purchase price (set value), but based on previous days closing price. For this report, I only care about the change day to day.

View 6 Replies View Related

Cell Formula Based On Conditional Formatting?

Dec 3, 2012

Say I have the following....

12345678910
59465465495652505853

I conditional format the top number which in this case is 65, how do I highlight the number directly above it? Either by another conditional format or some formula that picks the number out. I've tried HLOOKUP and VLOOKUP, but its giving me spurious results.

View 2 Replies View Related

Conditional Formatting Based On Adjacent Cell

Aug 26, 2013

Is there a way to shade a cell based on whether the adjacent cell is shaded? I found some ways to do this with VBA but I was looking for a non VBA solution if possible. So basically if cell C5 is highlighted yellow I would want cell D5 highlighted yellow too, regardless of the values inside the cell. Is this possible without VBA?

View 6 Replies View Related

How To Do Conditional Formatting Based On Two Cell Values

Jan 14, 2014

highlighting dates on a premade calendar.

The calendar is made as below:

Tasks
1-1-2014

1-2-2014

1-3-2014

[code]....

The data table is given as below:

Tasks

Date

Task1
1-3-2014

Task2
2-14-2014

I need to highlight the cell in the calendar that matches both the task and the date in the data table. For example- in the situation above, D1 would be highlighted.

View 3 Replies View Related

CONDITIONAL FORMATTING TO VBA. Row Format Based On Cell Value

Apr 19, 2008

I have set 3 conditional formatting rules where the row from column A to K will change depending on the value on column L.

Selection is equal to A3:K100, conditions are similar to:
- Formula is =$K3="PIP"

Then for formatting I chose WHITE BOLD FONT & BLUE SOLID PATTERN, for example.

Conditional formatting only allows 3 conditions. If this can be replicated
using VBA, then you can set "n" conditions, correct?

View 9 Replies View Related

Conditional Formatting Based On Time Of Day In Cell

Jul 27, 2006

I would like to use Conditional Formatting to format a cell depending whether it past a certain time, such as 3:45 PM. Naturally, entering the time as the condition does not work. Is there a way to do this?

View 5 Replies View Related

Conditional Formatting Based On Cell Content

Jan 3, 2008

I am trying to format cells based on what is in them across the board. Is there a way to conditional format based on what is in certain cells? I have 5 columns. I need to color in every NA only in the rows that have 1 or less cells with a number in there. So if there is 2 cells in that one row that have a number in them then leave the whole row white. If there is only 1 number in that row and the rest are NA then color all the NA's in red......

View 4 Replies View Related

Excel 2010 :: Highlighting Data Based On Conditional Formatting

Apr 1, 2014

I'm using Excel 2010 on my Mac. I would like to set up the data as follows:

A
B
C
D
E
F

NAME
DATA X
DATA Y
DATA Z
DATA XX
DATA YY

NAME
90%
88.2
410
88.4%
97.8

[Code] ........

Now what I would like to do is have the data in each column highlighted a certain color based on specific ranges. For example in Column B I want anything from 92% - 100% highlighted blue. I would like 83% - 91.9% highlighted purple, and anything under 83% highlighted red. Do I need to have a conditional format entered in each cell? Can I set up a conditional format for the whole column? Once I can figure the formatting in my example the remaining columns will be a breeze since they will be set up in the same manner just different number ranges, same colors will apply.

View 2 Replies View Related

Conditional Formatting Columns Based Off Cell Input

Jun 24, 2014

Instructions are in the attached excel as well. Please see attached

If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.

=Column B>Column C, then turn input cells in Column A Red.

First four highlighted are examples of thise formula scenario

** All based off an input into column

How to use conditional formatting to do so???

Excel Conditional Formatting 06.24.14.xlsx‎

View 5 Replies View Related

Conditional Formatting Based On Text In Adjacent Cell?

Feb 16, 2014

A2:A5 contains different dollar amounts, a6 is the sum of those amounts

In column B (B2:B5), i place a "p" beside the amount in column A once it has been paid.

I want to:

a. conditionally format the amounts in column a to turn green once I put the 'p' in the adjacent column

b. conditionally format the sum in A6 to exclude amounts that have been paid, and only display the total amount of the unpaid lines.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved