Hi Sue,

If the names are too long, then you could give each person an identifier, like a unique ID. This will allow you to sometimes use the ID if you want short labels and other times use their name, if you want to see names.

I would avoid concatenating the room number with their initials etc. as this could change if/when a person changes rooms.

The 'lookup' you want to do can also be affected by the date/period for those changing rooms, so I would be more inclined to use Slicers and a PivotTable to do this lookup. The PivotTable can contain the ward and room number, and the Slicer can contain the names. As you select the name it will return one or more room and ward combinations for people who have had multiple rooms. From there you could add a date/period field if required.

You can copy the rows for the prior month and paste them again in the table, then just edit those people with changes.

I hope that points you in the right direction.

Mynda

Thanks Mynda

I had not thought of an identifier - but a great idea.  Thanks

I'm not sure I fully understand "The 'lookup' you want to do can also be affected by the date/period for those changing rooms, so I would be more inclined to use Slicers and a PivotTable to do this lookup. The PivotTable can contain the ward and room number, and the Slicer can contain the names. As you select the name it will return one or more room and ward combinations for people who have had multiple rooms. From there you could add a date/period field if required." but I will try to do that and see how it goes.  Thanks

Sue

Hi Sue,

My 'lookup' comments were in response to this paragraph in your original question:

"I have been trying to link the name of the ward to the name/room number, so that when I type in the room number, the name of the person and the ward name will populate as well.  I can't get the formula correct for that."

Hope it makes more sense now, but I'm here if you have further questions.

Mynda

Hi Mynda

I'm clearly doing something wrong. 

I added the identifier to my "Risks list" tab This has the list of all the people with their room numbers and now the identifier.  I can get the first cell to populate correctly in the data worksheet, however, as there is more than one risk for most people, the identifier is the same for a number of cells.  When I put the same identifier in the next cell, it returns an incorrect name??  I am using xlookup as it sounded like it would work. 

I've changed the formula for the first two rows in column "E" in the data sheet - but haven't gone any further as I kept getting either an incorrect name or a blank cell. 

Thanks for any light you can shed on what I'm missing.  Maybe Xlookup is not the right formula for what I'm trying to achieve?

Thanks again

Sue

Hi Sue,

I would paste the Names in the Data sheet as values because the room data will change over time, so this shouldn't be a lookup formula. Then you can use XLOOKUP to return the ID based on the name column in the Data sheet.

Note: the names have leading and trailing spaces. You should clean this up. You can use the TRIM function to remove those spaces before pasting the existing VLOOKUPs that return the names as values. e.g.

=TRIM(VLOOKUP(....))

The attached file has the ID in the Data table. You'll notice there are some #N/A errors because some names are blank.

Mynda

Thanks Mynda - that helped.  I was looking at it the wrong way around.  I was trying to reduce the amount of data the staff have to input - thus using the room number initially to look up the person's name.

Is there a way I can stop the #N/A errors.  The names are blank as there are no people in those beds - so there is no name.  That will sometimes, but not always be the case.  Maybe we just put all the empty beds in one section at the bottom of the table, and only insert them in the table when there is someone in the bed?  What do you think of the attached?  Looks better than when I started.  Thanks for the assistance 🙂

Hi Sue,

Great to see you're making progress.

You can use IFERROR to hide #N/A errors e.g.

=IFERROR(VLOOKUP(...),"")

Mynda