dynamic range name in a data table

[spence]

i have a range named "Data" which refers to:
"=offset(B2,0,0,COUNTA(B:B),9)"
the column headings are in row 1, and i dont want them
included in the range name. but if the first record in
the table (row 2) is deleted, the "refers to" shows a
#REF error message. this first row may need to be
deleted at some point. is there any way to modify the
above formula to prevent this error in the event that the
first record is deleted. TIA

 

[Frank Kabel]

Hi Spence

change your formula to
=offset(B1,1,0,COUNTA(B:B),9)

HTH
Frank

 

[Peo Sjoblom]

Try something like

=OFFSET(INDIRECT("Sheet1!B2"),0,0,COUNTA(Sheet1!B:B),9)