Reference specific tables in specific sheets on a different sheet in the same workbook

[Sircotton]

So I have my formulas set up. I would like to include multiple months of data in multiple tables in different sheets (1 month of tables per sheet) and have the information counted on an Audit Total sheet
Sheet1 = Jan, Sheet2 = Feb, Sheet3 = March.
So I want to reference sheet 1 in January and Sheet 2 in February in Audit Sheet in the same workbook
Sheet1 = Jan
MedsNotCharted

Nurse Name	Med Name	Date
		1/1/19
		1/1/19

<tbody>
</tbody>

MedsChartedgreaterthan60

Nurse Name	Med Name	Date
		1/2/19
		1/2/19

<tbody>
</tbody>

Sheet2 = Feb
MedsNotCharted

Nurse Name	Med Name	Date
		2/3/19
		2/3/19

<tbody>
</tbody>

MedsChartedgreaterthan60

Nurse Name	Med Name	Date
		2/4/19
		2/5/19

<tbody>
</tbody>

“Audit Sheet” will have Multiple tables of the same information broken up into different sheets
Audit Total

A	B	C	D	E
Jan	Date	1/1/19	1/2/19	1/3/19
	MedsNotCharted	1	0	0
	MedsCharted>60	0	2	0
Feb	Date	2/1/19	2/2/19	2/3/19	2/4/19
	MedsNotCharted	0	0	2	0
	MedsCharted>60	0	0	0	1

<tbody>
[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]2/5/19
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]0
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]1
[/TD]
[TD="colspan: 2"]
[/TD]

[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]

</tbody>

Formulas
Jan
=COUNTIFS(MedsNotCharted[Date],">="&Sheet2!C$2, MedsNotCharted[Date],"<"&(Sheet2!C$2+1))
=COUNTIFS(Medsgreaterthan60[Date],">="&Sheet2!C$2,Medsgreaterthan60[Date],"<"&(Sheet2!C$2+1))
Feb
=COUNTIFS(MedsNotCharted[Date],">="&Sheet2!C$6, MedsNotCharted[Date],"<"&(Sheet2!C$6+1))
=COUNTIFS(Medsgreaterthan60[Date],">="&Sheet2!C$6,Medsgreaterthan60[Date],"<"&(Sheet2!C$6+1))

 

[RasGhul]

Hi Sircotton,

If I could make a suggestion, we could use something like indirect to pull data from various tables across multiple sheets but this may become slow and problematic later as your data increases. From what I can work out you have 2 lists of medications that you are tracking mednotcharted & Meds > 60. If your end goal is to be able to track counts per day/month or year it would be easier to do a register database on one sheet and use the countifs to extract for your audit table.

Data entry will also be easier as you only have to maintain one table instead of 24. Then you could also use pivot tables to extract data for different reports as required.

If you can upload to google sheets or dropbox a sample version of your sheet I can do a mockup version for you.

 

[Sircotton]

here is a link to the googlesheet: https://docs.google.com/spreadsheet...rKtUOZLaHx2v74A-r9073_vp1k/edit#gid=201335505
There are about 15 different audits that are performed. The sheet
i have has 5 of them.
I can send a more completed copy but wanted to make sure you can access it. Thanks

 

[Sircotton]

I am currently just auditing and counting by Date- How Many of each audit occur on each date.
I may expand to also counting how many Each Nurse (From a list of employee names) have in each audited category. Nurse had 5 Not Charted 4 Charted greater than 60 min and 3 Incorrect waste. This may be something I try to do in the future. Just giving this information to give a sense of what the goal is.
Thanks again

 

[RasGhul]

Have a look at this link of my mock-up, if you can use this setup I think it will make things easier later as more criteria is added;

https://www.dropbox.com/s/1d26qjgmlg53fao/Narc Audits_JP.xlsx?dl=0

 

[Sircotton]

The mock up is awesome. I think this will be perfect. Better than anything I imagined. I am going to input the rest of my categories/criteria and implement next month.
Thank you so much

 

[RasGhul]

Awesome thanks for the feedback

 

[Sircotton]

Hello. It's been a while. In using the spread sheet that you mocked up it has n=been great. I just had a couple of questions. I included them on the spread sheet on the database tab. Please see the link below. All of the functionality was not saved when I uploaded the sheet.
https://docs.google.com/spreadsheet...VFYOImYrVdbJ2vX45qAhAS4zE/edit#gid=2076876213

 

[RasGhul]

Hi,

You may need to post your questions here as I don't have access to your google sheets.