Dynamic List Sorting

I've posted a link in a the 'charting forum', but it's more of a general question stemming from a charting issue. Link to other forum is below for maybe more detail?

I've created a dynamic link called 'namelist' and it uses offset to dynamically change the size of the list as items are added or subtracted:

=OFFSET(sheet1!$a$1,0,0,COUNTA(sheet1!$a:$a),1)

Now I have a second sheet ("sheet2") that is very similar, but may have more or less entries than sheet1. I'd like to have this exact same dynamic list function for "sheet2", but the offset command references a SPECIFIC sheet, which really doesn't allow me to do what I want... is there a way around this?


http://www.excelforum.com/showthread.php?t=602018

just to add to this to make it more clear:

sheet1 has maybe:

A B
1 project title
2 ----------
3 name numb
4 bird 22
5 bee 40
6 bear 55

sheet2 has maybe:
A B
1 project title
2 ----------
3 name numb
4 pat 23
5 sue 20
6 doug 21
7 sarah 22

So sheet two has several more entries than sheet1.. I want both to have a dynamic list that is expandable or shrinkable (eg. dynamic).. .and I don't want to have to create a list (ie. 'namelist1', 'namelist2','namelist3') for each sheet (b/c I might have 25+ sheets at a time)

the issue is that the offset command below reference 'sheet1!' in it and therefore the same dynamic list can't be used for multiple sheet ??!?!?

=OFFSET(sheet1!$a$1,0,0,COUNTA(sheet1!$a:$a),1)

Originally Posted by VBA Noob

Using some XL4 and indirect functions I think you can do it

Set up a name called ThisSheet

and in the refers to: field enter

=GET.CELL(32+0*now();indirect("rc";False))

then name your area e,g DataArea

and in the refers to: field enter

=OFFSET(INDIRECT("'"&ThisSheet&"'!$A$1"),0,0,COUNTA(INDIRECT("'"&ThisSheet&"'!$A:$A")),COUNTA(INDIRECT("'"&ThisSheet&"'!$1:$1")))

VBA Noob

Hi VBA Noob,

Great job! I did not know that is possible.