Filtering Out Empty cells in Vlookup

[Pushppreet]

Hi,

I got a planner made from a member in this forum. I need further help.
I have attached the file for your reference.

There are 2 worksheets where the result can be extracted, from the "Calendar" worksheet, the first one is "Trng or Trainer Search" which is working fine. Second one is "Monthwise Search". In this worksheet the result gets populated from the "Calendar" worksheet as well. This is where I need some help. Out here, what I want is if I select the month from the dropdown box, I want only those training names to be displayed which have been planned for that month. Trainings with empty plan should not be displayed.

Lastly, Can I get another worksheet where I can have a monthly search for all trainers for a specific month (Like the second worksheet for trainings). For Example, If I select a month from the drop down box, names of all trainers from Bottom of "Calendar" worksheet gets displayed with what all trainings they are conducting in that month. With a total days they are busy, Off and Free...???

Would appreciate your help.

Regards,
Pushppreet

 

[Hui]

See attached

There is a hidden Column C on the Calendar Worksheet

To understand the formulas on Monthwise Search refer to: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
For the last question can you please setup a worksheet template of what you want

 

[Pushppreet]

See attached

There is a hidden Column C on the Calendar Worksheet

To understand the formulas on Monthwise Search refer to: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
For the last question can you please setup a worksheet template of what you want

Dear Hui,

Thanks a lot for your help...!

Could I even ask you for your help with something more advanced. I have added another worksheet and have written what kind of output I want. Would that be possible as well..?

Have attached the file.

Regards,
Pushppreet

 

[Chihiro]

For Month dependent lookup.

=INDEX(Calendar!$D$109:$ND$123,MATCH($B7,Calendar!$A$109:$A$123,0),D$3-DATE(YEAR(D$3),1,0))

Using date in Row 3 - 1 day before start of year = # of days = column index

Date(2017, 1, 0) is equivalent of 12/31/2016.

See attached.

 

[Pushppreet]

For Month dependent lookup.

=INDEX(Calendar!$D$109:$ND$123,MATCH($B7,Calendar!$A$109:$A$123,0),D$3-DATE(YEAR(D$3),1,0))

Using date in Row 3 - 1 day before start of year = # of days = column index

Date(2017, 1, 0) is equivalent of 12/31/2016.

See attached.

Hi,

Thank you for your help, I am having a problem with this file... If I add new training names by inserting lines in calendar, then the new training names are not reflecting in the monthwise view worksheet.

I have marked the training name in the Calendar worksheet.

Regards,
Pushppreet

 

[Chihiro]

Hmm? I see the training appearing in Trainer Availability sheet....


But, Pushppreet already has entry for Jan 1st... See PH Pushppreet in calendar sheet.



I'd suggest adding some sort of check to see if same trainer appears more than once on same day.