Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

conditional format data bars based on different cell

Posted on 2010-09-20
10
Medium Priority
?
378 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

718 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