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!

 

[zendog1960]

Sorry had a thread problem but the spreadsheet now shows. Please help if you can!

 

[Jeffrey Smith]

Countif works

The formula next to week1 below Phils name (Cell H2) should be

=IF(COUNTIF($A$3:$A$100,$G$1)>0,"X","")

This will put an X in that cell. You can make anything you want.

Hope that's a beginning.

 

[Domenic]

I'd suggest changing the layout. For example, assuming that A2:C7 contains the source data, let A11:A13 contain Week 1, Week 2, and Week 3, then let B10:I10 contain Phil, Brad, Guy, etc., then try...

B11, copied across and down:

=IF(COUNTIF(INDEX($A$3:$C$7,0,MATCH($A11,$A$2:$C$2,0)),"?*"),IF(ISNUMBER(MATCH(B$10,INDEX($A$3:$C$7,0,MATCH($A11,$A$2:$C$2,0)),0)),"P","A"),"")

Adjust the ranges accordingly. Is this acceptable?

 

[Jeffrey Smith]

Book1.xls
	A	B	C	D	E	F	G	H	I
1
2	Week1	Week2	Week3				Week1	Week2	Week3
3	Phil	Tom				Phil	P	P	P
4	Brad	Doug				Brad	P	P	P
5	Guy	Guy				Guy	P	P	P
6	Jim	Jim				Jim	P	P	P
7	Tony	Dave				Tony	P	P	P
8						Doug	A	A	A
9						Tom	A	A	A
Sheet1

In the example above the cell G3 should be

=IF(COUNTIF($A3:$A100,$F3)>0,"P","A")

I rearranged the cells a bit. It's easy to copy the formula to the other cells.

Jeff

 

[Jeffrey Smith]

Book1.xls
	A	B	C	D	E	F	G	H	I
1
2	Week1	Week2	Week3				Week1	Week2	Week3
3	Phil	Tom				Phil	P	A	A
4	Brad	Doug				Brad	P	A	A
5	Guy	Guy				Guy	P	P	A
6	Jim	Jim				Jim	P	P	A
7	Tony	Dave				Tony	P	A	A
8						Doug	A	P	A
9						Tom	A	P	A
10
Sheet1

Sorry I got that wrong. shoulda checked things a bit

Cell G3 should be

=IF(COUNTIF(A$3:A$100,$F3)>0,"P","A")

Jeff

 

[zendog1960]

Jeff's formula worked to a degree but it doesn't take into account that during the course of the function (34 Weeks long), there will be weeks that are not yet attended. This needs to be accounted for so other data can be used based expressly on the number of weeks that have been attended only.

The three conditions are:

A = Absent - the function happened but the member was not present
P = Present - the funtion happened and the member was present
x = Future date - Future dates of the function that have not yet happened therefore 0 values can not be use because it will mess with other calculations.

Is there a way, without creating a new layout (mine is created and populated with 6 weeks of data so re-layouts are not an option), to use the three conditions via a formula that would change the "A"s in Jeffs example above for week 3 to blanks?

This would be a tremendous help? I am pulling my hair out over here trying to figure this out, wait, I don't have any hair!

Thanks for all your help thus far. I know we are close just need that last kick!

 

[Bill H]

Since changing the layout isn't an option, you might try...

in H2:

=IF(COUNTA($A$3:$A$7)=0,"",IF(COUNTIF($A$3:$A$7,$G$1)>0,"P",IF(COUNTIF($A$3:$A$7,$G$1)=0,"A")))

You'll need to adjust the range/cell references to attain the desired results for the other attendees.

 

[Domenic]

Here's another way...

H2, copied down and to Column J:

=IF(MOD(ROW()-ROW(H$2),4)<>3,IF(COUNTA(INDEX($A$3:$C$7,0,MATCH(G2,$A$2:$C$2,0))),IF(ISNUMBER(MATCH(INDEX(G$1:G$16,(INT(ROWS(H$2:H2)/4)+1)*4-4+1),INDEX($A$3:$C$7,0,MATCH(G2,$A$2:$C$2,0)),0)),"P","A"),""),"")

Hope this helps!

 

[zendog1960]

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!