Harlan Grove wrote:
Alan Beban wrote...

Harlan Grove wrote:

...

=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65 536),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.

Hey, I took my shot at providing something useful for the OP and any
interested users. You didn't like mine and suggested a different
approach and that's fine--all the better for the users. But I'm neither
being nor playing dumb; it's your baby and I'm inclined to let *you*
think through and clean up your previously omitted details so that an
interested user can readily apply it.

Alan Beban