Dynamic Ranges and Offset
 

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

Dynamic Ranges and Offset
 

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

Dynamic Ranges and Offset
 

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

Dynamic Ranges and Offset
 

"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.