Vlook up in Popup Box?

[coreyjames]

I am working on a template for my co workers and so far everything is working properly, but there is one feature I would like to add. Currently when you exit my template a pop up box will appear if not all Hospitals are listed in our analysis. This lets the employee give a reason as to why all Hospitals are not present in the data. I would like to revise this code so that the popup box tells the employee which hospitals are missing from the analysis. Please see my code below.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("HospitalSummary").Range("b10") < 24 And Sheets("HospitalSummary").Range("a11") = "" Then
Sheets("HospitalSummary").Range("a11") = InputBox("You Must First Explain Why All Hospitals are not Present in Analysis") This is where I would like it to tell me which hospitals are missing
End If
End Sub

Private Sub Workbook_Open()

End Sub

Thanks!

 

[BrianExcel]

Something like this can be done, but keep in mind that a vLookup is only going to give you one value. If there are multiple missing hospital(s) as you indicated then vlookup will not work the way you want it to...

 

[coreyjames]

Great point. Would you be able to lead me in the right direction to make this work? Maybe implement an index look up?

 

[HalfAce]

I agree with Brian, vlookup doesn't seem the way to go for an unknown number of results.
I would look at looping through the list of hospitals, comparing that list with the list of hospitals that are not missing, and then return all that are not on that second list.
Where is your entire list of hospitals, and how are you determining which ones are not missing?

 

[coreyjames]

Halface,

How would I go about looping through the list of Hospitals (They are in a pivot) and compare them to a list I have on a tab called "Formulas"? Thanks

 

[HalfAce]

Hmmm I don't work with pivot tables so I'm afraid I can't help much there. But the list in the 'Formulas' sheet is half of what we need.
How are the ones in the pivot table entered in there?

 

[coreyjames]

Hmmm I don't work with pivot tables so I'm afraid I can't help much there. But the list in the 'Formulas' sheet is half of what we need.
How are the ones in the pivot table entered in there?

I used a vlookup and an if statement and now a column that lists hospitals that are misssing. How do I get the popup box to list the items in that list?

 

[coreyjames]

I didnt word that well. I almost have everything working but I would like to implement the following:

My current list:

Hospital 1
Hospital 2
Hospital 3
Blank Cell
Hospital 4
Blank Cell
Hospital 5

Is there a formula I can write that will put any cell that is not blank into a list as follows?:

Hospital 1, Hospital 2, Hospital 3, Hospital 4, Hospital 5.

I am thinking once I have 1 cell with all of the data I can figure out how to implement that into my input box.