Create a list with no blank cells

Somebody sent me an excel file containing a list of hospitals in Column A. In the first row, it listed 40 different types of rebates. For each hospital, only certain rebates were used.

He would like to create a dropdown list for each hospital, showing only the rebates that contain a value. For example, he would like the dropdown list to show only Rebate1 and 3 for hospital 1 because only these 2 rebate columns contains values. He asked if it can be done.

I have tried googling on whether I can create a list with no blank cells before and couldn't find a solution. But I deceided to try again. To my surprise, I managed to find a few websites that demonstrated how it can be done using formulas. One of them that explained how it can done can be found here.

I modified the formula to do it for columns. Then I setup the formula such that he can do a double selection, select an hospital first before showing the list of rebates. This involved the use of the all powerful OFFSET formula to complete the solution. To see how it works, download this excel file.