Dynamic ranges and consolidate

[marc005]

can someone please help me with the offset function for one of the worksheet in the below workbook.
When a new row or column is entered the range should expand. I tried something like:

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)

but it does not work! Once I get one sheet working I think I manage the rest.

https://dl.dropboxusercontent.com/s...gZfZaDuLS8155L0_q_kJZxHcX3GEKKkk02tToeyg&dl=1

 

[Weazel]

maybe...

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A),counta(1:1))

 

[marc005]

Weazel could you please have a look at the worksheet to see what would work?

 

[Weazel]

quite a few sheets and lots of columns....
lots of #REF errors in name manager too...
I'd delete them all and rebuild them....

something like the named range below should cover it.


You could shorten the 'height' counta maybe.

[TABLE="width: 751"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[TD="colspan: 8"]=OFFSET('USA (NYSE)'!$A$1,0,0,COUNTA('USA (NYSE)'!A:A),COUNTA('USA (NYSE)'!1:1))</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[marc005]

Thanx Weazel