Hi there

Just like to say thanks to everyone who answered my previous questions as
the spreadsheet now does what I needed to do but now I have another problem.

Quick recap

I have a spreadsheet that will contains a column for rank, name, points and
difference in the first sheet called Monday through to the 7th sheet called
Sunday.

The purpose of the spreadsheet is to show the points for 500 agents that
they have got so far in total. What I need to do is to highlight the agents
whose points have either stayed the same as the day before or have lost
points and highlight them

Sheet Monday


Rank Name points Difference
1 Ian 10000
2 Steve 9000
3 Chris 8000
4 Peter 7000
5 Leo 6000
6 Gus 5000
7 Phil 4000
8 Linda 3000
9 skinner 2000
10 Lucy 1000


Sheet Tuesday
Rank Name points Difference
1 Ian 110000 100000
2 Steve 9000 0
3 Peter 8900 1900
4 Chris 8800 800
5 Gus 6200 1200
6 Leo 6000 0
7 Phil 3789 -211
8 Linda 3000 0
9 Lucy 1020 20
10 skinner 892 -1108


This has been fixed by Francis who suggested
=IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) and a conditional formatting

Which works fine.

I now need a new column added explaining why there scores have stayed the
same or gone negative. This will require me to put A, I, i, or V in the next
column. This is something that I need to do every day. Its a bit of a pain
having to do this every day from scratch. I was wondering if there was a
formula that would automatically copy over the settings from yesterdays
sheet. A potential issue is that it would need to take into account the new
position that somebody would be in and also need to be flexible enough that I
can over ride it without deleting the formula.

Rank Name points Difference Reason
1 Ian 110000 100000
2 Steve 9000 0 V
3 Peter 8900 1900
4 Chris 8800 800
5 Gus 6200 1200
6 Leo 6000 0 I
7 Phil 3789 -211 A
8 Linda 3000 0 V
9 Lucy 1020 20 V
10 skinner 892 -1108 A

The 2nd problem is that i need a new worksheet that will show how many times
somebody has one of the new symbols. I.E I need all the agents that have an
symbol beside their names for 6/7, 5/7 of the work sheets. I would like it to
look like this for the various symbols

5/7 6/7 7/7
Steve
Linda
Lucy