I would like to have cell F11 display a specific value based on what the contents of cell C11 are.
For example, if cell C11 were to display “Mental Health”, then F11 should display “Paula”, whereas if cell C11 were to display “Pharmacy”, then F11 should display “Lisa”.
However, I should point out that the value of C11 could be any one of 39 values, and the values of F11 could be any one of 5 names.
Now, I know how to create nested IF statements, but given the volume of possibilities / data, this won’t work due to argument limits being breaches. I have named certain groups of C11 values (let’s call them Areas) with people’s names (F11 values) so that each person oversees a certain Area. The Areas are named something along the lines of lisaArea, paulaArea, etc..
The value of C11 is selected via a drop-down which I have inserted via the data validation tool and I require the value of F11 to update automatically to reflect which person is linked to the Area under scrutiny.
Please note that, ideally, I would like to be able to succeed with this without using VBA, but if there is no other way other then VBA, then so be it.
If any further info is required, please let me know.