Run a report based on a tag in a cell?

Welcome to the Forum BadDogJay!

I don't think we have a super hard problem here but I need a little clarification.

Why are the same room numbers repeated, with the same data? I see 101 repeated 6 times with Papa Smurf.

You have nothing in your file showing where you would pick the shower times, or what you want the report to look like. What is the purpose of your report? Are you trying to give staff a schedule so they can, for example, see who gets showers today?

Is there any limit for how many residents you can schedule for a shower in the same time slot? Your example shows two people in 10, but I assume there must be some limit.

Also you are making liberal use of merged cells. I cannot express strongly enough how much of a problem this can be. I see no benefit to how they were used in this sheet. I have shown a redesign that doesn't need them. I did leave the merged rows in column E since it's just text, but still suboptimal.

As a start I have added two shower times in the RM# column for each room. To do the report I need more input from you on what you want it to look like, and I will have to review what functions are available in your version of Excel.

I had a different understanding of the request, although Jeff's proposal seems quite good.
1. Columns U:W displays a list of shower numbers and times
2. Columns Z:AB display shower numbers assigned per room
3. Column Y displays In/Out and is populated using: =IF(INDEX(B$9:B$80,MATCH(Z3,B$4:B$75,0))=TRUE,"In","Out")
4. Cells S4:S15 display the room numbers associated with the tag in cell S3 using: =IFERROR(INDEX(Z$3:Z$28,AGGREGATE(15,6,(ROW(C$4:C$75)-ROW(C$3))/(AA$3:AB$28=S$3)/(Y$3:Y$28="In"),ROWS(S$4:S4))),"")
Note that the check boxes in cells B62:B80 do not work.
Let us know if you have any questions.

Thank you for replying so fast!

I should have mentioned I am the overnight nurse in charge of updating this report every night. It gets printed and distributed to various units. Throughout the day, the nurses hand-write in the "6-2, 2-10,10-6" columns. The only time the sheet will actually have any change to it is when I change the "documentation" column. That gets updated each night based on what has been hand-written on the paper copy. And it is only text, not data.

They were using a Word document for this but it is horrendous and I'm trying to modernize just a little. I threw in the "census" check boxes so I didn't have to manually keep track.

As far as the showers:

This is a separate report that gets printed & handed to the nurses aides. Shower information cannot appear on the main sheet. But, I thought I would take advantage of the fact that I have all the resident information in a format that can generate this report instead of me having to hand-write it every night.

I hope that makes sense -- I will be printing the shower information with room #, resident name, and shower time for each day separate from this sheet and physically distributing it. It's fine for the report to appear on a separate sheet within the workbook.

There are 4 units, each with AM and PM showers, and it ranges from 3 residents per shower to 9.

I haven't input the shower data anywhere because frankly I have no idea how to go about it. But currently, I look at the pre-dertermined schedule broken down by day, room number, and AM or PM. Then I have to either type or write it out.

As far as the duplicate Papa & Mrs. Smurfs, I accidentally duplicated page 1. A lot. Oops. There are about 30-40 residents per unit, and there are 4 units, so each unit will have their own page like this.

Does that help clarify things?

The reason I want to make this work is because if I can figure this out, I won't have to re-do everything when a resident "moves out"....

Thank you again!

To convert this to a printable report I feel that we need to modify the format of the Nursing Home sheet, however first take a look at the attached file and see if it is closer to what you envision.
1. Output sheet has tag #, day and time in row 3
2. Room numbers and names in rows 4:15
3. The data needed to support the output is on the Data sheet which could be hidden
Let us know if you have any questions.

Thinking about this a bit more I feel that putting only the data needed for the shower report on the Data sheet and doing away with the Nursing Home sheet would work best.
Attached is a sample.
Note that the In/Out designation is accomplished using drop downs instead of check boxes as I feel drop downs are easier to work with.
Let us know if you have any questions.

See if this is set up correctly.
On the Perfect World sheet cells A3 and C3 are the tag numbers. The values in those cells could be hidden by changing the font to white.
On the Nursing Home sheet cells in the range B9:B80 that previously contained check boxes now contain drop downs.
On the Data sheet the actual room numbers and tag numbers will need to be manually filled in (a process that should need to be done only once).
Let us know if you have any questions.

Is there going to be one shower report of all four units or will each unit have its own shower report?

There is no need to remove the drop down, just select the cell with the drop down and press the delete key to erase the In or Out displayed and leave the dropdown intact.
I have reapplied the formulas to the Unit 1 Shower Report sheet and set up a separate sheet for the Unit 2 Shower Report.
If you need help setting up the other Shower Report sheets please let us know.

I forgot to say that cells A3 and C3 are populated using formulas.
On the SR 1 sheet the formula in cell A3 is: =INDEX('D1'!A3:A16,MATCH(LEFT(A4,3)&A5,'D1'!B3:B16&'D1'!C3:C16,0))
On the SR 2 sheet the formula in cell A3 is: =INDEX('D2'!A3:A16,MATCH(LEFT(A4,3)&A5,'D2'!B3:B16&'D2'!C3:C16,0))
On the SR MC sheet the formula in cell A3 is: =INDEX(DMC!A3:A16,MATCH(LEFT(A4,3)&A5,DMC!B3:B16&DMC!C3:C16,0))
On the SR MC sheet the formula in cell A3 is: =INDEX(DA!A3:A16,MATCH(LEFT(A4,3)&A5,DA!B3:B16&DA!C3:C16,0))
On each sheet the formula for cell C3 is: =SUM(A3,1)
All seems to be working now.
Let us know if you have any questions.