I have a database which records a lot of patient information. one section of my sheet records the treating consultant followed by his/her speciality and then the name of the hospital he/she works at. I found that staff were spelling names etc. wrong so I restricted entries by using data validation which links back to a list on a seperate sheet in my workbook.
However, staff are still entering the wrong information as it relates to the doctor. To resolve this issue I was hoping that I could set up my workbook so that once the doctor is selected from the list it automatically fills the following 2 cells with the correct information.
If you look at the example I have uploaded, you will see that I am using named lists rather than cell references, I have to do this as my workbook is split into a lot of different sheets based on a patients location and it makes it a lot less time consuming to have my list centralised if i need to add people to my lists.
At the moment, sheet1 column A is linked to the list in sheet 2 column A from row 2-11 (named 'consultant'), sheet1 column B is linked to the list in sheet2 column A from row 15-21 (named 'speciality') and sheet1 column C is linked to the list in sheet2 column A row 24-27 (named 'hospital').
I would like if selecting a doctor from the list would fill the next 2 cells with the information as it appears in sheet 2 which was a reference list which i put together for the staff but nobody seems to use it.
Any help you could give me on this would be greatly appreciated, thanks in advance for your help.