Help with some formulas

[zendog1960]

I have a spread sheet I am trying to get working. Here is the form so far:

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1							Phil		Tom
2	Week 1	Week 2	Week 3				Week 1		Week 1
3	Phil	Tom			A = Absent		Week 2		Week 2
4	Brad	Doug			P = Present		Week 3		Week 3
5	Guy	Guy			(Blank) = Future Date		Brad		Doug
6	Jim	Jim					Week 1		Week 1
7	Tony	Dave					Week 2		Week 2
8							Week 3		Week 3
9							Guy		Jim
10							Week 1		Week 1
11							Week 2		Week 2
12							Week 3		Week 3
13							Tony		Dave
14							Week 1		Week 1
15							Week 2		Week 2
16							Week 3		Week 3
Sheet1

Here is what I am trying to do. Each week, any of the 8 people shown could be present at this function. Each week we write who is there. I would like to have a formula that will place in columns H or J, an 'A' for Absent for the weeks they are not on the list; a 'P' if they are present that week and a blank if t hat week hasn't yet happened.

The weeks that have not happened yet will be blank by default but if the fumula needs to be written to force the blank, I am ok with that. I thought vlookup or match would work but that only uses the data in the arrays and doesn't allow to manually place data that isn't in the array as far as I know.

Any thoughts? This is really perplexing to me and I am sure it really is simple. I just cannot get my head around this.

please help!

 

[Domenic]

Domenic, I am not sure why you are using the 'MOD' and "ROW' functions here. I am still learning Excel and all it's functions. Can you explain why the use of these two functions and why they might be better that say the formula that Bill H suggested. I have tried looking it up in help but we all know, the help doesn't always explain it in terms anyone except the programmers can understand.

I look forward to the explaination!

With the formula entered in H2, ROW()-ROW(H$2) returns 0. When copied to H3, it returns 1. So, the formula returns the following array of numbers for Column H...

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14

With the formula entered in H2...

MOD(ROW()-ROW(H$2),4) ---> MOD(0,4) --> 0

When the formula is copied to H3...

MOD(ROW()-ROW(H$2),4) ---> MOD(1,4) --> 1

So, the formula returns the following array of numbers for Column H...

0
1
2
3
0
1
2
3
0
1
2
3
0
1
2

Notice that 3's appear on rows where there's a name. So the IF statement is evaluated as true if the result returned by MOD(ROW()-ROW(..),...) is not equal to 3.

The difference between the two is that with Bill's solution the references will have to be changed for each week, whereas my formula can be copied to other cells without having to make any changes. Of course, the references, MOD divisor, etc., will need to be changed accordingly.