Dynamic Ranges and Offset

[RFJ]

I have a monthly spreadsheet - columns consistent but number of rows and
cell content not - that I need to link to a standard analysis sheet.

I've been trying to use dynamic ranges and offset formulae but am getting
unreliable results. eg :

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$25),1)

Part of it seems due to the fact that some of the cells in the early rows
can be empty. (eg $B$2 has a value of 1. If I delete it the sum of that
column changes by 11 !)

Does anyone know of a workaround or an alternative way of solving the
initial problem

TIA

 

[Don Guillett]

One way. Try something like this if numbers in col b
=OFFSET(Sheet6!$b$1,0,0,MATCH(999999,Sheet6!$b:$b),1)
"zzzzzzz" for letters

 

[Debra Dalgleish]

Is there another column that contains data in every row?
You could use it for the count.

 

[Leo Heuser]

I have a monthly spreadsheet - columns consistent but number of rows and
cell content not - that I need to link to a standard analysis sheet.

I've been trying to use dynamic ranges and offset formulae but am getting
unreliable results. eg :

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$25),1)

Part of it seems due to the fact that some of the cells in the early rows
can be empty. (eg $B$2 has a value of 1. If I delete it the sum of that
column changes by 11 !)

Does anyone know of a workaround or an alternative way of solving the
initial problem

TIA

RFJ

Assuming consistent columns are A:H, this array formula will do the job:

=OFFSET($A$2:$H$2,,,MAX(IF($A$2:$H$1000<>"",ROW($A$2:$H$1000)-ROW($A$2)+1)))

Set 1000 to a number you won't reach right away. The larger the number,
the longer the processing time.

The formula must be entered with <Shift><Ctrl><Enter>, also if edited later.

If you use the formula to make a named range (Insert > Name > Define),
just enter it with <Enter>