Alan Beban wrote...
Harlan Grove wrote:
....
=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$655 36),0)-1,4)

So your formulas don't work if the data range starts in Cell A1?

It's subject to the usual restriction that derived arrays can't span
65536 rows. If you were starting in row 1 originally, then you might
have though about changing the formula to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$1:$A$65535 ),0)-1,4)

but it appears you created the defined name as given, then moved the
data range from A2:D# to A1:D(#-1). I'll admit that isn't general
enough, so if the data range could start in row 1 and span all 65536
rows, then the formula should be changed to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65536 ),0),4)

If there were then no records in the range, only the column headings in
row 1, all the formulas would return #DIV/0! I don't consider that a
drawback, but if you do, you could take the uncharacteristic step of
showing how to trap it rather than playing(?) dumb.