conditional Formatting based on existing formatting

You can apply conditional formatting to colour rows based on word Total being contained in a particular cell of that row...

So, for example, you want to highlight Total rows in new columns D:F

Select the range of cells from D to F and invoke conditional formatting from the Format menu.

Then select Formula Is and enter formula:

=Search("Total",$A1)

where $A1 matches top row selected to format and represents column where the Total word is contained.

Then click Format and choose the matching colour.

The second part can't be done with conditional formatting, it would probably need formulas.. but you need to give more detail about the source data and what you need back.

NBVC,

Thanks so much for responding. I've attached a very small sample so you can see what I'me talking about (sorry if I didn't express myself very well). The paste from the Pivot is in black ink. A representative additional column is in red ink. My comments (what I need) are in green.

The real workbook has about 20 additional calculated columns. They're populated through calculations and VLookups, so I have to input the formulas then populate them down to the bottom of the data. Plus, the original pivot has up to 12K lines, which is why I don't like to do this all manually.

any help is muchly appreciated.

Thanks,
John

Try this formula in J3:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

Then select J3:J144 and apply conditional format formula:

=AND(ISERR(SEARCH("Grand Total",$B3)),SEARCH("Total",$B3))

When applying the conditional formatting, first select A3:I144
Does that work?

Originally Posted by jomili

Never mind my last comment; I was working on the opposite problem today. Your solution works EXACTLY as requested. Thank you so much.

Now, with that said, if someone pasted a pivot and DIDN'T color the Total lines, how would I go about coloring all of them from A to Last column?

You are wanting to format the Pivot table itself?

This formula applied to your conditional formatting will highlight rows that contain "Total" in cell A1 or column A if you selected a range, as ChemistB mentioned and applied it


=Search("Total",$A1)

Note that if you select a range starting at a different row, then change the 1 in $A1 to match the top most row you selected.

I think the formula should be:

Please Login or Register  to view this content.

I am not sure what I did on that first attempt.. of I didn't notice a glitch...

The formula starts by looking to see IF(B3="Grand Total"

if it does then it sums according to SUMIF(B$2:B2,"*Total",J$2:J2) which means sum all values in column J from the first row to the row above the current row where column B ends with the word "Total".

If it doesn't, then it goes to next step IF(ISNUMBER(SEARCH("total",B3)) and looks to see if the column B cell in current row contains "total"... if it does then SUM(INDEX($J$2:J2,MATCH(2,1/(B$2:B2<>""))):J2). This part sums all values in column J from row where the last non-blank cell occurs in column J above current row to the cell in column J above current row. This part MATCH(2,1/(B$2:B2<>"")) finds the last non blank row in column B from B2 to row above current row....

If column B doesn't contain "total", then it goes to this part: LEN(SUBSTITUTE(D3," ","")) which was your original formula to calculate number of characters in the string with no spaces....

I hope that helps and apologize if the original formula confused you...

You're very welcome.

I am not sure if this is one of the websites you visited on your quest to learn the Index/match combination, but here is a good one to show you some basics:

http://www.contextures.com/xlfunctions03.html

Contextures is a good website for you to get to know alot of functions and how they work independently and with other functions for added functionality and complexity.

Most of these "combined" functions are learned by watching these forums, because people in these forums have done alot of trial and error playing and testing and have come up with these great working combinations... no book will have most of these formulas in them. You've got to love Excel to really be persistent and learn and try different things and understand how you can combine things to get the desired outcome. It is amazing how much you really can do with this program

that is why my signature contains the phrase...Where there is a will there are many ways.... It really applies with this program...

Good luck.

Here are a couple more websites I like for learning how functions work independently and together with others:

http://www.xl-central.com/index.html

http://www.techonthenet.com/excel/index.php

Both.. amazing sites, in my opinion

There are also a couple of sticky threads in the Excel General forum that have hundreds of useful links...

We are here to help... and if asked, most of us endeavour to give an explanation, because we appreciate that some people want to learn and not just be spoon fed. We were all there once too... and believe it or not, we are all still learning something new everyday on this forum (don't let the Moderator/Guru/MVP status fool you into thinking we know everything there is to know about Excel)...