I tested again and find that it work fine on the condition that each of your
ranges is the same size. If columns A, C and F have data in exactly the same
rows it works. If data is missing from one cell you get unequal range sizes
and the formula does not work. If you know that one column will contain a
complete set of data while the other two don't you might change your formula:

Assume SURGDT will always have a date even though there may be blanks in
PHYNO and TYPE. Consider these range names:
PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$A:$A),1 )
SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 )
TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$A:$A),1 )

I haven't tested this but I think it should work. This is a rather fragile
setup you have.

Tom