Need help automating my sheet

[Nebul0us]

I need help automating this sheet. I want to highlight the winning team on the left, then have the losing teams automatically highlighted for every person. After that I want the total highlighted cells to be counted and output at the bottom for each person. Please see my example I hope I made sense....

 

[Nebul0us]

Unfortunately, those sites are blocked for me (for the same Corporate Security Policy I mentioned earlier...)

That is why we usually recommend the tools included in my link - the maximum number of users will be able to see it. Otherwise, you may be limiting your pool of potential helpers.

I simply used the built-in "insert image" button on this forum...

Can anyone that is not limited by their corporate settings help with my OP?

 

[Joe4]

I can see the image from home (which is where I am right now).

Unfortunately, Conditional Formatting runs off of cell's values, not manual formatting. So I don't know of any way of doing what you want without doing a bunch of VBA, which I think will then have to be called to run manually.

If, however, you entered the winner into some cell, instead of just highlighting it, you could use Conditional Formatting to do all your highlighting without having to use VBA.

 

[Nebul0us]

I can see the image from home (which is where I am right now).

Unfortunately, Conditional Formatting runs off of cell's values, not manual formatting. So I don't know of any way of doing what you want without doing a bunch of VBA, which I think will then have to be called to run manually.

If, however, you entered the winner into some cell, instead of just highlighting it, you could use Conditional Formatting to do all your highlighting without having to use VBA.

I guess the VBA is why I am here. I could do the extra cell with the winners....but how would I then total number wrong (highlighted) at the bottom for each person?

 

[Joe4]

OK. I cannot see the sheet again because I am back at work, so I am just going to set up a simple example to show you how to get your counts if you use a new column for the winners.

Let's say that you insert a column into column A for your winners, and columns B and C are the listing of the two teams.
Now let's say that your first person is in column D. And the games are listed in rows 2-17.
Then, to count the number of matches (games they got right), you can enter this formula in cell D18:

=SUMPRODUCT(COUNTIF(D2:D18,$A2:$A18))

If you instead want to return the number they got wrong, just subtract this number from the total number of games, i.e.

=COUNTA($A2:$A18)-SUMPRODUCT(COUNTIF(D2:D18,$A2:$A18))

I think that should give you what you want.

To read more on the method I used for the counts, see this: https://exceljet.net/formula/count-total-matches-in-two-ranges

 

[Joe4]

Note: Another trick you can do to make it so you can simply select the winner instead of having to type it is to use Data Validation.
For example, if you wanted cell A2 to list the entries in B2 and C2, then:
- highlight A2,
- select Data Validation
- in the Allow field, choose the List option
- in the Source field, enter this formula: =B2:C2
- copy down for all rowsNote that you will want to type in the Source field and not select the cells (selecting with include absolute range references, so when you copy it down, it wouldn't adjust and all would be looking at B2 and C2).

 

[Peter_SSs]

Apart from some people not being able to see the images, nobody can copy/paste from them and many potential helpers will simply bypass your thread because they (understandably) don't want to be bothered typing out a whole heap of data to test with. The alternatives Joe has pointed you to can be copied from so give you a much greater change of getting helpers - and more quickly.

It was the same issue for me, so I have just made up a small example, which also relies on an extra column (A) to hold the winner. Then all the coloured cells get that way with the Conditional Formatting shown, & a slightly simpler formula to count each person's losing choices.
- Select B3:C?? and apply the green CF
- Select D3:End and apply the losing CF
- Formula in D10 is copied right.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Winner	Home	Away
3	KC	KC	NE	NE	KC	KC	NE	NE	KC	KC	NE	NE
4	BUF	NYJ	BUF	NYJ	BUF	NYJ	NYJ	BUF	NYJ	NYJ	BUF	BUF
5	ATL	ATL	CHI	CHI	CHI	CHI	ATL	ATL	CHI	CHI	ATL	ATL
6	BAL	BAL	CIN	BAL	BAL	BAL	CIN	BAL	BAL	BAL	BAL	BAL
7	PIT	PIT	CLE	CLE	CLE	PIT	CLE	PIT	CLE	CLE	PIT	CLE
8	DET	AZ	DET	DET	DET	DET	DET	DET	DET	DET	AZ	AZ
9
10	Missed			4	2	2	4	1	3	3	2	3
Losers

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	1. / Formula is =B3=$A3	Abc
D3	1. / Formula is =D3<>$A3	Abc

 

[Nebul0us]

OK. I cannot see the sheet again because I am back at work, so I am just going to set up a simple example to show you how to get your counts if you use a new column for the winners.

Let's say that you insert a column into column A for your winners, and columns B and C are the listing of the two teams.
Now let's say that your first person is in column D. And the games are listed in rows 2-17.
Then, to count the number of matches (games they got right), you can enter this formula in cell D18:

=SUMPRODUCT(COUNTIF(D2:D18,$A2:$A18))

If you instead want to return the number they got wrong, just subtract this number from the total number of games, i.e.

=COUNTA($A2:$A18)-SUMPRODUCT(COUNTIF(D2:D18,$A2:$A18))

I think that should give you what you want.

To read more on the method I used for the counts, see this: https://exceljet.net/formula/count-total-matches-in-two-ranges

Okay so this will work but how would I compare a range of cells, then skip a row, and include another cell of a column to that of another column? Skipping the row seems to mess me up. I'm using =SUMPRODUCT(COUNTIF(E3:E17,E19=$C$3:$C$17,$C$19))
How can I skip row 18?

 

[Joe4]

Okay so this will work but how would I compare a range of cells, then skip a row, and include another cell of a column to that of another column? Skipping the row seems to mess me up. I'm using =SUMPRODUCT(COUNTIF(E3:E17,E19=$C$3:$C$17,$C$19))
How can I skip row 18?

Maybe write two separate equations (one for each range) and simple add them up.

=Formula1 + Formula2

 

[Peter_SSs]

How can I skip row 18?

What is it about row 18 that means it needs to be skipped? Is it at all related to the image you originally posted (the first game from Monday night)?
If not, could you post another small set of sample data (preferably one we can copy from) & explain in relation to that? We could then also see what E19 and $C$19 have to do with it.

 

[Nebul0us]

I think I got it to do what I needed. Not as pretty as it could be with some VBA, but works for me. Thank you all for your help I really appreciate it.