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

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
--
Don Guillett
SalesAid Software

"RFJ" wrote in message
...
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

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

RFJ wrote:
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







--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

"RFJ" skrev i en meddelelse
...
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


--
Best regards
Leo Heuser

Followup to newsgroup only please.