F
Fireblade
I have several lists which I've defined dynamically, such as:
List1
=OFFSET(A1, 0, 0, COUNTA(A1:A5000),1)
List2
=OFFSET(B1, 0, 0, COUNTA(B1:B5000),1)
so that they will change automatically when I add or remove items.
I'm trying to allow the user to choose a list and automatically gives
them statistics from that list. A2 on Sheet2 has a data validation
list of all list names. A6 is supposed to display the sum of all
entries in the chosen list. The formula I have for A6 is:
=SUM(INDIRECT(A2))
This gives me a #REF! error. It works if I define the list statically,
such as $A$1:$A$342, but not if I do it dynamically as above.
Is there any way to use INDIRECT for a dynamic list?
List1
=OFFSET(A1, 0, 0, COUNTA(A1:A5000),1)
List2
=OFFSET(B1, 0, 0, COUNTA(B1:B5000),1)
so that they will change automatically when I add or remove items.
I'm trying to allow the user to choose a list and automatically gives
them statistics from that list. A2 on Sheet2 has a data validation
list of all list names. A6 is supposed to display the sum of all
entries in the chosen list. The formula I have for A6 is:
=SUM(INDIRECT(A2))
This gives me a #REF! error. It works if I define the list statically,
such as $A$1:$A$342, but not if I do it dynamically as above.
Is there any way to use INDIRECT for a dynamic list?