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

Sircotton

New Member
Joined
Mar 26, 2019
Messages
10
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))
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi,

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

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top