Summarise data to only show non blank cells - from Rows not columns

Hi Everyone,

I'm trying to do as title states and show non blank cells from data in a row.

On the attachment, the first area are all formulas that pick up the names from a larger sheet - so Bob will always be in the first column, Mark in column 2, Sue in column 3, Terry in column 4 and Kelly in column 5. (In my full sheet there are actually 64 columns of employee names.)

So the area of Result Required, summarises who is on holiday - max of 5 can be off in any one day hence Name1 to Name5 - without the many blanks that can be in between.

The formulas I've found seem to all want to work with data in columns, so cannot find anything that will work for rows.

So wonder if anyone out there knows of a way to do this - I don't understand VB so formula if poss.
Any other ideas or another way to format the sheet etc would be great.

Thanks
T

Hi Pepe,

I'm using xl2000, and it's coming up with #NAME? error - I gather this function is maybe for a more up to date xl version.

Thanks