Solved

conditional format data bars based on different cell

Posted on 2010-09-20
10
354 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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