Solved

conditional format data bars based on different cell

Posted on 2010-09-20
10
348 Views
Last Modified: 2012-06-27
I want to use conditional formatting with data bars based on the value of a different cell.  The cell to show the data bar is a text cell in the same row as the cell with the value.  ie; cell D14 contains the item description and cell X14 contains the amount.  I want the data bar in the cell with the description based on the value in X.  Possible?

Thanks
0
Comment
Question by:acdecal
10 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 33719782
Which version of excel are you using?

-Brandon
0
 
LVL 33

Expert Comment

by:jppinto
ID: 33719784
Please take a look at the sample attached file to see if this is what you want to do...

jppinto
Conditional-formatting.xls
0
 

Author Comment

by:acdecal
ID: 33719786
2007
0
 

Author Comment

by:acdecal
ID: 33719797
ippinto,
nope I want to use the data bars in 2007
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 33719809
I think the data bar needs to relate to the value in the cell. You might be able to use a workaround by using an adjacent column to D14, e.g. in E14 use the formula
=X14
and then set up the data bar on E14, choosing the "show bar only" option so that the number doesn't appear in E14
Any good to you?
regards, barry
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:acdecal
ID: 33719838
barryhoudini,

thanks but I don't have the real estate for that.  The description cell is long and would visually accommodate the data bar format.  
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33720661
You can't use the data bars in any cell other than the one containing the value, as Barry said. If you can't use an additional column next to the description cells, then the only other thing I can think of would be to use the camera tool to take snapshots of linked CF cells on another sheet for example, but that would be a nightmare to maintain.
Can I ask why you need the bars in the description cells?
0
 

Author Comment

by:acdecal
ID: 33720920
As data is added to the workbook, we would like a visual indication of how each line item contributes to the overall total.  The line item total is on the far right of the worksheet and normally out of site.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33720996
Then I'm afraid, as has been said, it's not possible with conditional formatting. You might be able to recreate a similar effect using autoshapes an adding rectangles to the description cells but again, I think maintaining that would be a nightmare. (I'm assuming you can't simply move the value cells)
0
 

Author Closing Comment

by:acdecal
ID: 33849315
A shame MS can't implement that.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now