I have a document which I need to have a variable auto populate function. I've attached the bare bones of the workbook that includes a front page and page named Patient 1.
What I'd like to achieve is as follows

• I plan to set the front page as a locked page with data coming from the patient pages (there will be 18 in total)
• If I enter a name in cell D3 and (for example) 1 in cell B5, I'd like the name to appear in cell B2 on the front page
• but if I have to change the room number on the patient page (cell B5) I would like the name in D3 to move to the new room number on the front page
• doing it this way I don't have to move data from sheet to sheet as this will introduce potential errors, especially as the workbook will have multiple users some of which are capable of deleting the internet in error (so to speak), so it needs to be as simple as possible
• I have set a hyperlink to take the user to the correct sheet from the front sheet, can the hyperlink be set to move with the changing data in the room cells (front page) to correspond to the appropriate sheet? i.e. if occupants of rooms 1 and 5 swop over

I look forward to your thoughts on this

Attached Files

psagV7.xlsx (18.3 KB, 13 views)