Help with reporting on data sheet.

[Satele]

Hi all,

I'm really struggling with this time-wise, though I know it can be done. I have two workbooks, one with my data, one with a report template. For simplicity, I've put some sample data from the data workbook in a separate sheet of the report workbook (attached if this is too confusing to follow).

My data worksheet can not be altered, and I need to use formulas on the report sheet to pull back data from the data sheet in an automated way.

In my data sheet, there are 10 columns of data:
A = UID
B = RecordType
C = HCode
D = AdmittedDate
E = Forename
F = Surname
G = DOB
H = Sex
I = STDate
R = RDate

An example of my reporting sheet is in the table below. What I need this sheet do might be fairly straight forward. If not, here's an explanation. Under 'Jul-12', I need the sheet to pull back the number of 'Assessments' and 'Reviews' completed in that month (reviews are in the form of 'transfers' and 'discharges' - 'Reviews' would just be a SUM of these two).

So far, this is fairly simple and could be achieved with SUMPRODUCT OR VLOOKUP. However, the difficulty is when I want to pull back the reviews, these need to be not just any review completed in that month but only reviews for the same patient who had an assessment, as determined by the 'UID' column in the data sheet.

Hospital Report
Reporting Quarer
Hospital Name	Hospital Code	Record Type	Jul-12	Aug-12	Sep-12
St George	STGE1	Assessments
		Reviews
		Transfers
		Discharges

<tbody>
</tbody>

An example of my data sheet:

UID	RecordType	HCode	AdmittedDate	Forename	Surname	DOB	Sex	STDate	RDate
89654	STAssess	STGE1	01/01/2012	Andrew	Macbeth	13/07/78	m	08/07/12
87676	Transfer	STGE1	01/01/2012	Mark	Jones	18/01/67	m		08/08/12
89654	Transfer	STGE1	01/01/2012	Andrew	Macbeth	13/07/78	m		12/08/12
87637	Transfer	STGE1	01/01/2012	Steve	Jones	09/05/86	m		17/08/12
87676	STAssess	STGE1	01/01/2012	Mark	Jones			02/07/12

<tbody>
</tbody>

So, in my report table above, the hospital code 'STGE1' should pull back the number 2 next to 'Assessments' under July. The formula should look for any records in the data sheet with a valid July 2012 date in the 'STDate' column. This part, I can do easily enough.

However, next to 'Transfers' in my report sheet, I only want to pull back the number of 'Transfer' records in the 'data' sheet with a corresponding 'Assessment' under 'RecordType'. i.e. the one with the UID 87637 Should not be counted. I also need the formula to only pull records that occur in the month as typed in the month for that particular column of the report sheet (cell D4: July 2012). All assessment dates are in the column 'STDate' and all review dates are in the column 'RDate'.

Can someone please help with this? I'm banging my head off a wall and need a fairly quick resolution!

Thank you,
S.

 

[oldbrewer]

I suspect that the reason you have had no replies yet is that you have made your question horrendously complicated.

Do you want to count ALL STdates for EACH Hcode for EACH month and likewise for Hcodes ?

 

[Satele]

I suspect that the reason you have had no replies yet is that you have made your question horrendously complicated.

Do you want to count ALL STdates for EACH Hcode for EACH month and likewise for Hcodes ?

Hi oldbrewer,

Thanks for your reply. I realise it seems complicated, but couldn't think of a more succinct way to explain this.

Basically, the answer to that is yes, with one caveat. In the cell under ‘Jul-12’ and adjacent to ‘Assessments’, I do want to count all STDates for that particular HCode mentioned in the report for each month (in this case, July 2012). That’s straightforward enough.

What I then want in the cell adjacent to ‘Transfers’ is to a count of all records with an RDate for the particular HCode listed in B5 (STGE1) in each month, but only if these records have a corresponding ‘Assessment’ record for that month, counted in the cell above. I don’t want to count any records with an RDate for that month if they don’t have a corresponding record with an STDate, counted above.

The one unique field to a record is ‘UID’.

Apologies if I’m not being clear and thanks for your help.

 

[oldbrewer]

if you need a fairly quick solution I think you will need an intermediate sorting out of your data - I cannot see how to do it in one neat swoop.

	Count of RecordType	RecordType
	UID	STAssess	Transfer	Grand Total
	87637		1	1
	87676	1	1	2
	89654	1	1	2
	Grand Total	2	3	5
						this simple pivot table can find all UID with transfer and assessment, so 89654 and 87676 can be ref points of a sumproduct formula that also screens by date month

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Satele]

if you need a fairly quick solution I think you will need an intermediate sorting out of your data - I cannot see how to do it in one neat swoop.

	Count of RecordType	RecordType
	UID	STAssess	Transfer	Grand Total
	87637		1	1
	87676	1	1	2
	89654	1	1	2
	Grand Total	2	3	5
						this simple pivot table can find all UID with transfer and assessment, so 89654 and 87676 can be ref points of a sumproduct formula that also screens by date month

<tbody>
</tbody>

Thanks. Would there be a way to create this pivot table as a separate sheet that knows how to reference the 'data' sheet automatically? That way, I could base my formulas off the pivot table, without user intervention.

 

[Oeldere]

Normaly the pivot table is set up in a new sheet, when you make them.

The pivot table is linked to the data-sheet.

You have to refresh the pivot table, if you add new data to the data sheet.

Excel 2007 => data => refresh

 

[Satele]

Thanks. On a related note, is there a way I can set up my formula to count all records with 'STDate' occurring in the month selected in D4, E4, F4. So for July, the data is in the format 02/07/2012 and I'd like all dates within 01/07/2012 - 31/07/2012 to be included.

 

[oldbrewer]

see my post #4 the months can be column headings and the "found" UID numbers can be row headings - just put in a suitable sumproduct formula

 

[Satele]

I don't think I can use the pivot table solution. The 'data' sheet is actually contained in a separate .csv file. I've only included a sample of it in this workbook so that it was easier to write the formulae, then I was just going to change the references.

That's why I'm trying to find a formula-based solution.

 

[oldbrewer]

if you have a csv file you can set up a spreadsheet to pull in that data automatically, and then use that spreadsheet as the source for the pivot table.