zendog1960
Active Member
- Joined
- Sep 27, 2003
- Messages
- 459
- Office Version
- 2019
- Platform
- Windows
I have a spread sheet I am trying to get working. Here is the form so far:
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!
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!