How to structure data that changes often

OshaK ✭✭✭✭

I wanted to ask the community to see if anyone has a similar issue or has any idea how to solve it.

I have 3 columns in the sheet:

  1. Attendings Involved (it's a drop-down menu with multiple value choices - choices don't change often)
  2. Attending not listed (First & Last Name) (currently free form text field)
  3. Fellow/s involved (First & Last Name) (currently free form text field)
  4. Resident/s involved (First & Last Name) (currently free form text field)

I'm able to make a chart "Number of Cases per month/Attendings Involved" because the data is structured.

the columns 2-4 can have multiple names separated by comma.

However, column #2 - can be random because it could be Attendings from other departments and I can't predict it.

columns # 3 and 4 - the Fellows and Residents come and go every year and Fellows can change several times a year.

  1. is it possible to chart the random names in column # 2?
  2. is there a way for me to chart either random multiple names separated by a comma for columns 2-4, or structure Fellows and Residents, so the data retains after they leave but for the submission form, they only see the relevant to them choices. In other words, is it possible in the drop-down menu to have something like

2022 (hidden in the submission form bc it's in the past, but saved in the master sheet)

Past Resident 1

Past Resident 2

Past Resident 3

2023 (only these choices are available to choose from in the submission form)

Current Resident 1

Current Resident 2

Current Resident 3

Or any other ideas on how to structure the often changing data?

Thank you!