Automatic Data Validation on Horizontal row which has filtered data from another row

Hi, I am completely new to VBA, and I have been looking for a solution for data validation list that changes according to the selected entry whenever the source is changed. I have come across a website which i am not allowed to post but have specified in the attachment below where she has provided a solution. However, the code is for when the source is changed manually, and only when there are no blank cells between entries in the source. So I thought I would be able to override this by filtering my source for blanks and putting it in another row and changing the code to view it as the new source. However, doing so doesn't work because the code doesn't register changes due to formula (because of Worksheet_change usage maybe?) I tried using Worksheet_calculate but Target returns empty and having no idea how the code works, I'm pretty much stuck. Could anyone help please? Also, I'm getting a warning whenever I try to save the sheet saying that a compatibility problem might be in place due to a spill or possible future spill?

Edit(The original excel guru for the website is no longer providing vba support)