Highlight row if data is in more than 1 column

[TAPS_MikeDion]

Hi everybody,

As always, I'm sure there is a simple conditional formatting formula for this, but I can't seem to get it to work without making a very long formula.

All I need to do is highlight a row if there is more than 1 column in that row that has data in a cell. I have 20 columns and 76 rows.

Thank you!

 

[Joe4]

Use this Conditional Formatting formula (for row 1):
=COUNTIF(1:1,"<>")>1

So, you would just select all the rows you want to apply this CF to, and write the formula as it applies to the very first row in your selection.

 

[TAPS_MikeDion]

Hi Joe,

Thanks for the reply.

My fault, I neglected to mention that the columns being checked are from D to T. Using your formula has every row highlighted since there is data that will always be in columns A - C. Sorry about that. I just realized it was going to cause an issue when I was entering your formula.

 

[Joe4]

Rather trivial update to the formula:
=COUNTIF($D1:$T1,"<>")>1

 

[TAPS_MikeDion]

Again, the entire row is highlighted without any data being in columns D - T.

WAIT...let me guess, it's because there's formulas in the cells, right?
Crud. How do I work that one in?

 

[Joe4]

You seem to be leaving out lots of important details!

Why don't you tell us all the details? It is very difficult to program things if we don't know what we are working with.
What exactly do these formulas look like?
What values are being returned in these cells?

 

[TAPS_MikeDion]

Sorry about that, I've been flying between jobs here and didn't focus as well as I should have on this thread I started.

I have a "Summary" sheet that pulls information into 17 columns from 17 location sheets. The location sheets (3-letter sheets - i.e. BGE, PAH, PDD, etc) have the employees first and last names, along with the days of the month (1-31) where the employees worked hours are entered (see example).

In the summary sheet, which Fluff helped with to get the formula needed for each cell in the 17 columns, the location sheets are listed by column (see example.)
If an employee worked at more than 1 location (i.e. there are hours showing in more than 1 column on the Summary sheet for that employee, then the row needs to be highlighted.



I'll make the examples short and sweet.
Location sheet (called BGE) example:

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
Doe	Jane	x	8	8	8
West	Tom	x				8	8
Yelle	Bill

In this example, Jane worked at location BGE on the 1st-3rd of the month at 8 hours each day, while Tom worked at that location on the 4th & 5th for 8 hours each day. Again, there are 17 of these "location" sheets in the workbook. WAL = Worked At Location. If there's no X, then the person didn't work there (this is for sorting.)



Location sheet (called PAH) example:

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
Doe	Jane
West	Tom
Yelle	Bill	x	8		8		8

In this example, only Bill works at location PAH and worked 8 hours on the 1st, 3rd & 5th of the month.



Summary sheet example:

LAST NAME	FIRST NAME	FIXED	BGE	PAH	PDD	ETC	ETC
Doe	Jane		24
West	Tom		16
Yelle	Bill			24

In this example it shows the hours each employee worked at each location (only BGE & PAH were used so the others are empty.) Fixed just equals fixed pay and has an X if that's the case.

The cells below each location sheet column have the code from Fluff (seen in bold blue below) to pull the hours worked data in from all of the location sheets.
There are 76 rows and the columns go up to AH.

=IFERROR(SUM(INDEX(BGE!$D$2:$AH$76,MATCH(A2&"|"&B2,INDEX(BGE!$A$2:$A$76&"|"&BGE!$B$2:$B$76,0),0),0)),0)

I need help highlighting each row with hours showing in more than one location column, please.

Again, I was zipping between jobs and did not give this thread the proper info needed for anyone to accurately assist me and I apologize again for that.

Hopefully this clears it up and you're able to assist me further.

Thanks Joe!

 

[Joe4]

OK, so it looks like in those cells with formulas you are checking, they are either returning numbers or nothing, right?
If so, then perhaps this will do what you want:
=COUNTIF($D1:$T1,">0")>1

 

[TAPS_MikeDion]

Correct, and thank you again Joe.

It works perfectly!!!

 

[Joe4]

You are welcome!

Glad it worked. Once we knew the kind of data we were working with, we were able to figure out how to make it work for you.