I have a question re auto populating sells on different sheets.


I've attached a rough example of what I'm trying to do and I guess some form of VBA/macro will be needed

here is what I'm trying to do

• Cells B2 -13 have data validation list drop downs showing room numbers
• cells C2-13 have user names
• I'd like to auto pop the users into the relevant rooms on the room layout sheet
• e.g. link the name in C2 to the corresponding number in B2
  • paste the date from B2 in B2 on the room layout sheet

this is where the challenge begins, although the names in C2 - 13 won't move the room numbers in B2 - 13 will, so when I change the persons room number, I'd like the name to move to the new room in the room layout page.

e.g. Bert is in rm 3, cell D2 in the room layout sheet, if I change his room number on the front sheet to rm 9, his name will move to H4 on the room layout

Is this possible?

Attached Files

Test.xlsx (10.3 KB, 10 views)

Hi Steve
See attached solution.
Basically I have used VLOOKUP functions to check what name is in Column C of the "Front" sheet and put that name in the room box on the next sheet.
In addition I have put a small macro that recalculates the workbook when anything changes on the "Front Sheet". The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlManual
For Each sh In ActiveWorkbook.Sheets
sh.Calculate
Next sh
End Sub
Instead of changing the room numbers, leave them static and just type the name of the person using each room against the relevant room number.
You can view this code by right clicking on the Front sheet tab and selecting view code.
Hope this helps.
If you need anything else then come back to the forum.
Good luck.
Tony

Attached Files

Test.xlsm (16.8 KB, 16 views)

Hi Tony
You have helped me on a first incarnation of this project which has linked data and formulas.
I've only included the first 2 columns for the test, the working document I want to add this to has additional data from cols D onwards.
In reality to save having to move significant amounts of data from specific rows and other linked pages, it's the room numbers that need to change

Hi Steve
In that case the solution I gave you will still work. However, for example, if Mary is in Room 2 and you change James to Room 2 on row 10 it will only pick up the first occupant of Room 2 which may not be correct.
hope this makes sense.
If you need any further help I would be glad to help.
Good luck.
Tony

I think I've got the gist of this.
tomorrow I'll copy and post a blank of the original workbook to show how I want to integrate this.
Once I had got my head around the original work you did for me it was easy to expand on it, this is a little beyond me for now, not too strong on macros/vba.
I've found this site to be a valuable resource for this project

Here is the template of the document I'm using, as previously mentioned I want to link the names from the front sheet to the room numbers on the occupancy page. The room numbers can change from day to day, so I need changes made to room Nos to reflect in who is in what room on the occupancy page

I thought I'd got Tony's method fixed in my head but no, it went down the plughole when I tried to transpose his test.xlsm to this workbook

Attached Files

PSAG Book 6 test 1.xlsm (163.9 KB, 10 views)

Hi Steve
I have just done Patient 1 only. Let me know if this is what you need.
Please bear in mind that on Patient 1 you had a space after the "1" which would prevent VLookup from working.
Also if you dont want anyone to change data in column I, suggest you protect that column so no changes can be made.
Good luck.
Tony

Attached Files

PSAG Book 6 test 1.xlsm (173.2 KB, 16 views)

It doesn't appear to have populated the Room Layout (room occupants tab) page,
When you say a space after the '1', not sure where you mean
Col 1 is locked

Hi Steve
I am currently working on your solution and will post it once I have finished.
Tony

Hi Steve
Please find herewith the update Workbook.
I have taken the liberty of adding a small piece of code that checks if a room number is already in use on the FRONT SHEET and blank the cell if it is until you correct the error.
If you need any further help then please let me know.
Good luck.
Tony

Attached Files

PSAG Book 6 test 1.xlsm (179.8 KB, 17 views)

Hi
Had a look and still can't seem to get it to work.
I guessed that if you clear a cell from the front page it will clear the corresponding occupancy cell, but it doesn't seem to want to do that, similarly, when you change room numbers it doesn't make any changes.
Am I doing something wrong?

Hi Steve
Make sure you have automatic calculation turned on for the WHOLE workbook. I believe the Room Occupants Sheet is set to Manual calculation, that is why it is not updating when you change the FRONT SHEET.
Tony

Hi Tony, that fixed it
Now I have to try and work out how you did it in case one of the users manages to mess it up and I need to make repairs
Many thanks
Steve

Hi Tony
That works a treat.
If at some point I need to change the Occupancy layout at a future date, would applying the formula =IF(ISERROR(VLOOKUP(C3,'FRONT SHEET'!$C$5:$D$25,2,FALSE)),"Empty",VLOOKUP(C3,'FR ONT SHEET'!$C$5:$D$25,2,FALSE)) and changing the cell details be sufficient.

The reason I ask is to
a) grasp what you actually did, to learn from you
b) a colleague is most likely to ask me to set the same up for the ward next door which is of a different floor plan

Hi Steve
Check your private messages.
Tony