Solved

Excel 2010 Conditional Formatting with dynamic linked table

Posted on 2013-12-23
4
400 Views
Last Modified: 2014-01-10
Hello,
I have a dynamic linked table which is working fine under Excel 2010
in lines I have names of shop
in columns year (2010,2011,2012,...)
the result is the amount of sales of the year per shop
I'd like to make a conditional formatting that will simply help me to identify if the result is growing or regressing regarding to the last column (in our case, the last year)
or to use the conditional format data bars
the problem is that I want a conditional formatting for each line and not for all the cells.
I have more than 800 lines so I can't do it manually.

Thanks for your help
0
Comment
Question by:bruno_boccara
[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
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39737263
...the problem is that I want a conditional formatting for each line and not for all the cells.

Can you explain this a bit further?

Do you have an example of the data and how you would like them formatted?
0
 

Author Comment

by:bruno_boccara
ID: 39737539
2010     2011     2012    2013
shop1         50         60         55        52
shop2        100       200       150      250
shop3       400        500       600     550

this is a sample of the table
if I choose to format with data bars all the cells, the bars will be between the value of 50 (2010 / shop1) and 600 (2012/shop3) and this is not good for me
I want data bars (or any else format) for each shop , that means
for
shop1 [50 - 60]
shop2 [100 - 250]
shop3 [400 - 600]

in fact, I want each line format to be separate to the other lines.
I have more than 800 lines ...so to do it manualy is excluded.

regards.
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39747124
Select the values in the first line (shop1), columns 2010 to 2013, and format using the databar conditional format.
Copy the line, mark all the lines for columns 2010 to 2013, insert special, select formats.
Then you get databars that are separate for each line.

Or if you want to see the development (increase-decrease) from year to year, it can be done with just one conditional formatting.
Select all values in the columns for years 2011 to 2013 and make 2 conditional formats, one for increase and one for decrease, using the formula option.
Make the formulas for the first cell in the range, shop1, year 2011.
See attached.
Conditional-format-each-line-Q28.xlsx
0
 

Author Comment

by:bruno_boccara
ID: 39770623
Your answer didn't resolved my problem because it is not possible to copy line with dynamic linked table.
But it help me a lot to understand the subject.

Regards.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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‚Ķ

751 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