Articles

Solved Multiple Conditional Formatting for Pivot Tables

December 13, 2012 at 10:44:32
Specs: Windows 7

I am working in a pivot table within excel 2010. There are three columns I am working with, 2 of which are dates, the third is a column with a number. I am trying to find the difference between the first two dates and if the difference is larger than the third column with the number, I would like to format it a certain way within the conditional formatting options. The true issue is that every formula I know, gets messed up when the pivot table changes. I need something that stays consistent within the pivot table's data changes.

See More: Multiple Conditional Formatting for Pivot Tables

Report •


✔ Best Answer
December 14, 2012 at 11:29:54

Okay - so we need to see how the access table is formatted. The field for the translation must be Numerical. Can you change the Access table?

Also - is the Pivot done in Access or are you importing the tables into Excel?
never mind - I just saw your first line of your first post.

But we should look at how you are getting the information from Access to Excel.



#1
December 13, 2012 at 12:42:37

Hi Tori

It's a little difficult to picture what the precise problem is. I would guess that what you are doing is creating the pivot and then adding additional column next to the pivot to calculate the difference and this is what gets messed up. Is this what's happening?

If it is, I think you can resolve your problem without the additional columns by including the calculation you want in the conditional format. For example, if you have your dates in columns C and D and your number in column E you can have the conditional format say

=$D2-$C2>$E2

This would eliminate the need for the additional column next to the Pivot table.

If this is way off the mark - could you paste an example of your data with further explanations? (when you paste your data please use the "pre" button)

Let us know...


Report •

#2
December 13, 2012 at 13:00:36

Thanks for your response. I was trying to add an additional column because every conditional formatting option I have tried does not work within the pivot table. However if I can work within the table, this would be preferable. I have even considered going back to the Access database and trying to bundle the dates together, but I'm relatively new at Pivot Tables and Access.

My columns are as such:

Column L = P1, P2 or P3 (Stands for Priority type)
Column M = Used a formula to turn column L into numerical value such as P1 = 3, P2 = 7, P3 = 14 (number represents days)
Column N = Date 1 - Date Project is presented
Column O = Date 2 - Date of Completion

I want to conditionally format either one cell or the entire row if Date 2 (O) - Date 1 (N), is greater than Column M's different numerical values corresponding with their priority.

Ultimately I want to show when we have surpassed our deadline to complete a project based on it's priority. For example, If I have a project that is P1 (L), which represents in 3 days due (M), the difference between my completion date (O) and my start date (N) should not be greater than 3 days (M).

I have tried to conditionally format with the formula as described above, but it has not worked. Do I need to select the data in a particular way?

PRICING_PRIORITY	P1-4 IN DAYS	PRICE_EFFECTIVE_DT	PRICING_COMPLETED_DT
P3	14	7/13/2012	7/23/2012 16:53
P1	3	11/29/2012	11/30/2012 13:32
P3	14	8/15/2012	8/17/2012 15:43
P2	7	7/27/2012	8/8/2012 13:06
P3	14	10/9/2012	10/23/2012 13:38
P3	14	9/24/2012	9/27/2012 14:18


Report •

#3
December 13, 2012 at 13:49:28

Hi Tori

It worked for me with this conditional format

=$O2-$N2>$M2

Applied to this range

=$L$2:$O$7

Notice that the first row of the range must equal the first row of the data being analysed - in this case row 2. Also notice the relative referencing - with the "$" in front of the Column and nothing in front of the Row.

I'll try to post pictures of what I've done ...

Here is a picture of the data that should match your arrangement (date formats are altered for my neck of the world). It also shows the result of the conditional format -----> 2012-12-14_0850

Here is the Conditional Rule and the format I specified -------> 2012-12-14_0852

Finally, this is the range it is applied to - this can be adjusted to whatever range you like. ------> 2012-12-14_0853

This is the data as it appears in the spreadsheet. Let me know if it is not the same as you have

PRICING_PRIORITY	P1-4 IN DAYS	PRICE_EFFECTIVE_DT	PRICING_COMPLETED_DT
P3	14	13/07/2012	23/07/2012 16:53
P1	3	01/11/2012	30/11/2012 13:32
P3	14	15/08/2012	17/08/2012 15:43
P2	7	27/07/2012	08/08/2012 13:06
P3	14	09/10/2012	23/10/2012 13:38
P3	14	24/09/2012	27/09/2012 14:18


Report •

Related Solutions

#4
December 14, 2012 at 09:48:20

Unfortunately this is still not working for me. I tried this exactly as you stated, however my row starts on the 5th row.

Used formula in conditional format

=$O5-$N5>$M5

Then under Manage Rules, I changed the range to below to get a bigger sample data.

=$L$5:$O$25

It is still not working. I did have a previous issue with a former excel document and I needed to restart in a new excel in order to get my formatting to work. Perhaps this is a glitch/virus.


Report •

#5
December 14, 2012 at 10:27:17

Hi Tori

It's a mystery. 2 things come to mind...

Make sure that your dates are in date format. In another column try the formula

=M5-O5-N5

to confirm that you are getting what you expect.

Also, could there be other conditional formats that may be interfering with this one? Just to be sure, when you click on Conditional Format, the 2nd to last choice is Clear rules. Click on that and then re-enter this rule. Also, try different formats this time e.g. fill with a noticeable colour and font that is not "Automatic"

Beyond those checks I'd start thinking about the "Repair" facility and possibly re-installing Office.


Report •

#6
December 14, 2012 at 10:40:11

Good idea with the dates, I've updated to "short date" form.

Yup, I've cleared all the rules, and added this one again. Still didn't work.

If I do =M5-O5-N5 it does not get what I need. I'm assuming because the numerical value in M is not a date like the other two values. However O5-N5 does work correctly. I could format this new column, it would just be frustrating to use with the pivot table when changing the parameters. I would either need to apply the formula to the whole row and get a bunch of "O's" or need to reapply formatting whenever the data changes, which I'm hoping to avoid.

Thanks for your help. I'll see what I can do with a repair. Thank you!


Report •

#7
December 14, 2012 at 10:46:56

One other thing that I just tested out. If the data you are trying to format is a pivot table then there are some special rules that apply to them. To get past those rules start your conditional rule outside the pivot i.e. if your pivot starts in M4 start your rule as M1 and your range starting in row 1 as well.

Report •

#8
December 14, 2012 at 10:54:10

WAIT!!!!! That;s the answer - your translation of prioritiy is not a Number!!!!!

(I'm shouting really)

Show me your equation that translates Priority to a number of days - you prabably have a space in there.


Report •

#9
December 14, 2012 at 11:21:20

Could be... I actually don't have an equation, because again the pivot table wouldn't keep the info as it changed data.

However in Access I created a table that give my values P1 = 3, P2 = 7, P3 = 14 and P4 = 21.

The value is listed in the Pivot Tables Row Labels. However it is not formatted to a number.


Report •

#10
December 14, 2012 at 11:29:54
✔ Best Answer

Okay - so we need to see how the access table is formatted. The field for the translation must be Numerical. Can you change the Access table?

Also - is the Pivot done in Access or are you importing the tables into Excel?
never mind - I just saw your first line of your first post.

But we should look at how you are getting the information from Access to Excel.


Report •

#11
December 14, 2012 at 11:43:03

GENIUS!

Although, now I am having one more issue using the same formula we started with. It is highlighting my rows, but it's not the correct ones. It is actually randomly highlights, some priorities are being met while other are not!

For example it is highlighting the first two rows, but the difference in days in the first row is 10 but we actually have 14 days to complete. Same with the second row.

I am using:

=($O8-$N8)>$M8 applied to =$L$5:$O$1000

P3	14	7/13/2012	7/23/2012	10.70414352
P1	3	11/29/2012	11/30/2012	1.564386574
P3	14	8/15/2012	8/17/2012	2.655092593
P2	7	7/27/2012	8/8/2012	12.54586806
P3	14	10/9/2012	10/23/2012	14.56821759

Do I need to put $ in front of the first equation's numbers???


Report •

#12
December 14, 2012 at 11:59:04

No - everything is off by 3 rows because your rule starts at O8 and your range starts at L5

Start your range at L8

Or change your rule to O5


Report •

#13
December 14, 2012 at 12:00:27

Just did. Everything works! Thank you so much, this was driving me crazy all week....

I really appreciate it!


Report •

#14
December 14, 2012 at 12:03:21

That's great because it was starting to drive me crazy too.

Report •

#15
December 23, 2012 at 19:25:54

http://www.herostart.com/productsho...

Report •


Ask Question