cleaner method?

[poporacer]

I am trying to create a report and I have it done, but it is very messy. I am hopeing there is a cleaner way of getting this done. Here is what I have and the issues:

I have a resident worksheet that has the following columns:
Name, ID#, Room, Date Arrived, Date Review, Date departed.

I have a shower worksheet that is used to track if residents are taking a sufficent number of showers. On this sheet, I want the Name, ID#, and Room to be in the cells automatically. I was able to do this by using =Residents!A2, etc
The first problem is that if Residents!A2 is blank then I get a 0 in the cell. I can hide the 0 by conditional formatting the font to white,but it makes another formula more difficult to achieve and printing will print all the cells if they have data. In other words, I initially have 10 residents, but for expansion issues I have 70 cells referenced to =Residents!A2, when I print the sheet, I will get several "blank" pages. Is there a better way to do this? I want to be able to create the sheet and not have to add rows/formulas when I get more residents. (I might not be the one using the workbook and want it to be simple to use. Any suggestions?

 

[Gavona]

Maybe you could avoid printing the shower sheet directly. Use it as the source for a pivottable (and in the pivottable hide if Name = 0)

Are you recording just the date/time of the latest shower or of all showers for all residents? If the latter then maybe there is a better way to structure your data....

Gavin

 

[poporacer]

If I do a pivot table, that creates another sheet which might work, but I am going to have staff filling out the form and some staff are not that computer literate so I want to make it as simple as possible. On the Shower sheet, it just documents each day of the week and whether they took a shower or not. I included a link to the workbook.

 

[poporacer]

for some reason the link didn't work right...here it is,

http://windyacresranch.net/storage/Patient yard-shower.zip