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, 11 views)

I take it you plan to have many worksheets, that is, the Front Sheet plus one for each patient. You envision each "room" cell in the Front Sheet searching through the patient worksheets looking for the corresponding value in B5, and (if it finds it) dynamically plugging in the patient name there. Is all that correct?

I've never done that with lookup functions, and I strongly suspect it isn't possible. If someone else here tells you "sure, just supply the range of worksheets in this syntax....", then by all means try it. But until that happens, I'm going to claim that the only way to do it is by writing your own worksheet function, or some other kind of VBA program within the workbook.

Now, I enjoy that kind of thing; but most people find the thought intimidating, and if you don't want to tackle it I won't sneer. But if you're already a programmer then this won't be hard for you; and if you're already thinking about being a programmer, or at least about trying it out to see how it goes, I encourage you to give it a whirl. Let me know, in that case, and I'll coach you.

The front sheet will be the graphical display/interface to summarise where people are sleeping, the important data being on the individual sheets.

As the rooms have varying speciality equipment, individuals may/will need to move to other rooms due to changes in support needs.

So when I change the room number (B5) of any individual sheet to another room, I need the name in D3 (individual sheet) to move to the corresponding room cell on the front page e.g. D2, E2 etc

Yeah, that's what I understood you to be saying—just wanted to be sure. So if you want to do that, I think you need to create some VBA code. Some people here will help you with that if you can get yourself started, and as I said, if you're willing to tackle it and don't know how to start, I'll coach you. (I admit, I like teaching; not everyone does.) I don't see how you're going to get it done with built-in worksheet functions.