I'm having a hard time figuring out how to display a report from a database of answers from a survey type review.
The database goes into a worksheet called WTP.
Row 1 is column header/database field names.
Column A is a unique ID
Column I is one of three centers ID codes, either "4640", "4645" or "4660"; yeah text not numbers.
Column P is the Review Date; mm/dd/yyyy
Columns S through CK are answers to each question. The column titles are the same as the field names in the DB. In each column the possible values are "Y", "N" or "N/A".
There are several comment fields in the middle of that range that are not to be included because they just contain free text comments.
Okay, now for the good part...
The report has meaningless question numbers in column A (11, 11a, 11b, etc.)
The full text of the Question is is column B e.g.: Quote:
Special Projects Only: Was special project training category and applicable training hours documented in the participant's file? (y, n, n/a)
Column C will just have a calculated %, D13/G13.
Column D has Code:
[code]=COUNTIF(INDEX(WTP!$
A:$CK,0,MA
TCH($O13,W
TP!$A$1:$C
K$1,0)),"Y
") [/code]
where $O13 is where I Transpose Copied down the list of Column titles from the data sheet. This counts "Y"s in the column matching the title in $O13.
Similarly, Column E has Code:
[code]=COUNTIF(INDEX(WTP!$
A:$CK,0,MA
TCH($O13,W
TP!$A$1:$C
K$1,0)),"N
") [/code]
and this counts "N"s in the column matching the matching the same data field/column header title.
And of course, Column F has Code:
[code]=COUNTIF(INDEX(WTP!$
A:$CK,0,MA
TCH($O13,W
TP!$A$1:$C
K$1,0)),"N
/A") [/code]
which counts the "N/A" answers for the same column.
Then in Column G is just a total of Ys and Ns in columns D and E.
So far this has just totaled the Y, N and N/A answers to the single question which is in the data column which matches the text in $O13.
This is repeated of each question. There may be as many as 80 questions. (yeah, I have several of these things)
That part is done and I'm okay with, though I'll take suggestions for improvements.
The part I'm struggling with is that for each question the VP wants the "Y" and "N" answers reported by Month, say for three months; Jun, Jul, Aug. Number of Y and N for each month. AND by Center; "4640", "4645" and "4660". The dates are all just in column P and the Centers are just all in column I in no particular order.
Quote:
"Oh, and make it look nice, not too cluttered."
I wanted to tell him the report would take a year to prepare and cost $90,000.00, but I didn't.
I don't think it can be done, but I'm sure someone will prove me wrong.
Anyway, if I can't get this report figured out, does anyone know if anyone is hiring in the Fort Lauderdale area?
Thank you in advance. I know this is a tall order.
