Ignoring blank cells in results

Occasional Contributor

Hi All, I've attached a test sheet based upon our food bank worksheet. We have over 300 families in the rows and over a year of weekly food deliveries in the columns. I can generate results using IF, as on the test sheet (Ive also used vlookup and hlookup). With small numbers of rows and columns this is OK but what i need to do is produce reports of who was fed on a particular date - but without all the blank lines of families that were not fed and similarly for each family a list of the weeks they were fed with out all the blanks of weeks they were not fed. Thanks in advance.

6 Replies
Try this:

Hi Patrick, thank you so much for your help. Your suggestion works perfectly on the columns, so I can get a report based on selecting a date. I have tried to modify your solution to search horizontally but failed misserably. What I would like to be able to do is request a list of dates that say family 'b' were fed. Please can you help me again. Thanks Simon.
Try this one:

C29 is the cell containing the family letter.
Hi again Patrick, thanks so much again, this also worked perfectly on my little test page. For some reason when i rewrite both these on the actual sheet the lists produced are not correct, I've obviuosly made a mistake somewhere but cant see it at the moment!
Is it off by 1 row? Possibly a different arrangement with the header?
Again, THANKS! there were other columns with phone numbers etc. between the reference and the first date. Both reports working perfectly now. Thanks again!