Advanced Data Validation

[rebe29cca]

I have three sheets that show data for hospitals and their physicians. The sheets show different time periods. One sheet shows month over month, the other quarter over quarter and the third, year over year. They all look identical other than the different time periods. In A4 I have a data validation that lets the user choose a hospital. In A5 I have a data validation that lets the user choose a physician, based on the hospital they chose in A4.

My problem is that when they choose a hospital and physician in the sheet "MOM Data" I want this same choice to come across automatically in the sheet "QOQ Data" & "YOY Data" so the user doesn't have to choose the same hospital and physician in each sheet. However, I want the user to be able to go to any of these three sheets and choose a different hospital/physician combination. They shouldn't have to go back to, say, "MOM Data" and change it here, but rather if they are in "YOY Data" and decide they want to see a different physician, they can change it here and it will then change the other sheets to the same hospital/physician combination.

[FDibbins]

Hi, welcome to the forum

1. you cannont "link" a DV on 1 sheet to a DV on another sheet
2. 2007 does not allow DV to be used across sheets, only in the sheet it is on

If you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like, I will see i I can come up with something for you

[rebe29cca]

Sorry, I need to change my profile I guess. But I'm using excel 2013 now. Does that make a difference?

[FDibbins]

Upoad a sample please

[rebe29cca]

Here is the sample file. Lets see if I uploaded this right

[FDibbins]

If all you want to do is pull out the data for a single hospital/doc combo, use this instead...
=INDEX(B:B,MATCH('QOQ Data'!$A$4&'QOQ Data'!$A$5,INDEX(Lookup!$B:$B&Lookup!$C:$C,0),0))

[rebe29cca]

I have the lookup for the data all worked out. What I need is a way for the user to choose a hospital and then choose a doctor associated with that hospital. I have that built in now. The problem I have is that the user has to go to each report page to choose their hospital and doctor. Essentially they need to do this three times and then if they need to select another hosp/doc they have to do this again three times.

I need them to be able to choose their hosp/doc from any of the three report pages and have it change it on the other two automatically.