Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

conditional format data bars based on different cell

Posted on 2010-09-20
10
Medium Priority
?
408 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
Independent Software Vendors: 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

963 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