10-09-2019 11:12 AM
10-09-2019 11:12 AM
Hello everyone! I recently started using Excel for personal use by creating budget worksheets. I have learned quite a bit and want to keep learning. In my job we have been using a Word table as a form to update, per shift, a list of patients on the unit. This table gets destroyed by those who are not familiar with using tables on Word. So every few days I spend a lot of time reformatting it. I had a thought that Excel would be a more suitable program... I am starting to think I was wrong. I have managed to get all that I need and much more on this form with the exception of 2 or 3 things. I am curious to whether this can be done.
Ok, So there are 24 patients on the unit. I have used 8 rows to create one room.
I need this format to be continued for, at the very least, 24 times. Sometimes there will be a need to have more than 24 if we move patients off the unit and then bring in new ones. I can get the original 24 on here just by copying and pasting... BUT if I add rows in between previously made rooms, how can I get Excel to insert this blank format you see in the picture?
This is the biggest obstacle I have before this form is good to go. You will make my day and my managers too if we can make this work. If it isn't possible, give me the best alternative.
10-09-2019 02:52 PM
10-09-2019 05:25 PM
Thanks for reading and reaching out to attempt to help me! Sorry if I wasn't clear. Maybe this new screenshot will help. I created a blank worksheet that we will fill in with patient data. I have created some cells that have drop down boxes with choices to input data (trying to decrease keystrokes and standardize data). Other cells I have CountIf formulas to tally certain responses that will be on the very first page of this document. The remaining cells will be free text input. I learned how to have my column titles ( lack of the right terminology ) to be repeated at the top of every page. So I am not worried about that row.
It's the 8 rows that will have the patient data in them that needs to be repeated.
The text you see in the third column needs to be in their respective cells throughout the document. Once the document is filled with the patient data there will be times when I need to add another room (8 rows) in between the two rooms. Is there a short cut way to add rows and then excel will keep the same format (borders, formulas and drop down boxes, etc) and most importantly keeping the text that is in the third column?
I know the long way of it such as inserting a new row, copy the cells (room) above and then pasting it in the new row. At this point I will have to erase all of the duplicate data that was copied. This also creates turmoil for those not so computer savvy individuals.
Look forward to your thoughts, Kimberly
10-10-2019 12:07 AM
Excel keeps all that (format, borders, drop down, etc.) when you copy a range of rows into a new location. You can also paste in the middle of the table. (see attached video)
What you could do is have one "Master Table" with your 8 rows and always use this as the source when adding a new patient, this way you don't have dublicate values
I hope I understood your issue....
10-10-2019 01:09 PM
I recommend that you store the Information about Patients in Sheet1, Treatment in Sheet2, and Summary in Sheet3. Ensure that data are entered only once. Subsequently, they are either selected from a drop-down list or extracted through an appropriate LOOKUP formula. That is the essence of good spreadsheet design.