Solved

conditional format data bars based on different cell

Posted on 2010-09-20
10
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 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