List upcoming Birthdays (Sheet 1) from Client Database (Sheet 2)

[Blomberg]

Hello dear Excel Guru's!

I want to create a listing of upcoming birthdays (within 30 days or the 5 soonest) on my sheet "Client Overview" (Cells: S24 to S30) - from my sheet "Client List", where the date of birth is listed in Cells G2 to G11 (and growing).

Ideally, I would like to list on my "Client Overview"-sheet, in Cells S24 to S30, the Firstname + Lastname and Date of Birth, which resides on sheet "Client List" in cells B2:B11, F2:F11 and G2:G11 respectively.

Either with formulas or VBA.

So far, I have conditional formatting set up on the actual table on "Client List".
However the actual listing I have not managed to get working. I am suspecting an array formula, or some INDEX & MATCH-combination?

Does anybody have an idea of how this would be possible?

Thank you kindly in advance!

 

[Blomberg]

Update:

I managed to extract the name from the table using an INDEX function and list the matches with an array formula.
However - I don't know how to work around the blank cells that are being filled in when there is no match.

Right now I get a result looking like so:

Upcoming Birthdays (within 100 days)
FirstName1
""
""
FirstName2
""
FirstName3
""
""
""
""
FirstName4


I used the following formula:

=IF((DATEDIF(TODAY();DATE(YEAR(_tblClientList[Date of Birth])+DATEDIF(_tblClientList[Date of Birth];TODAY();"y")+1;MONTH(_tblClientList[Date of Birth]);DAY(_tblClientList[Date of Birth]));"d"))<100;INDEX(_tblClientList;(ROW(_tblClientList[Date of Birth])-1);2);"")

The first part:

DATEDIF(TODAY();DATE(YEAR(_tblClientList[Date of Birth])+DATEDIF(_tblClientList[Date of Birth];TODAY();"y")+1;MONTH(_tblClientList[Date of Birth]);DAY(_tblClientList[Date of Birth]));"d"))<100

- is simply calculating the remaining days until a persons birthday, and checking within the IF if the number is days is less than 100.

The second part:

INDEX(ROW(_tblClientList[Date of Birth])-1);2)

- returns the value of the first name (same row, but a few columns to the left), using the INDEX-function, grabbing the first name located in column 2.

_tblClientList is the name if the table.
[Date of Birth] is the column name within the table.
I used the above as an array formula.

The issue persists that it lists each person from the table in the cell range, simply filling in a blank ("") where there is no match.

Any ideas?

 

[Blomberg]

Anybody?