Adamski
11-12-2009, 03:18 AM
Hello,
I want to use an array of sheet names in formulas. I had a custom vba function to loop through all my sheets and add the appropriate ones to an array and return it. That worked but was too slow since it was looping the sheets everytime the function was used.
So I wanted to have a global array of the sheet names, populated once on Workbook Open. Unfortunatly it appears you can't have global arrays.
So I thaught I'd have a global string of the sheet names eg:
"{Sheet1;Sheet2;Sheet4;Sheet5}"
or
"{'Sheet1';'Sheet2';'Sheet4';'Sheet5'}"
or similar.
How do I then use the string in a formula?
The INDIRECT function dosen't work
Is there a way to convert a string representing an array to an actual array.
eg.
=SUMPRODUCT({1;2;3;4;5;6;7;8;9})
works
=SUMPRODUCT("{1;2;3;4;5;6;7;8;9}")
fails with #VALUE!
=SUMPRODUCT(INDIRECT("{1;2;3;4;5;6;7;8;9}"))
fails with #REF!
I want to use a custom vba function to return the string
eg. something like (if the above actually worked!)
=SUMPRODUCT(INDIRECT(GetArray()))
Or perhaps there is a better solution?
Thanks
I want to use an array of sheet names in formulas. I had a custom vba function to loop through all my sheets and add the appropriate ones to an array and return it. That worked but was too slow since it was looping the sheets everytime the function was used.
So I wanted to have a global array of the sheet names, populated once on Workbook Open. Unfortunatly it appears you can't have global arrays.
So I thaught I'd have a global string of the sheet names eg:
"{Sheet1;Sheet2;Sheet4;Sheet5}"
or
"{'Sheet1';'Sheet2';'Sheet4';'Sheet5'}"
or similar.
How do I then use the string in a formula?
The INDIRECT function dosen't work
Is there a way to convert a string representing an array to an actual array.
eg.
=SUMPRODUCT({1;2;3;4;5;6;7;8;9})
works
=SUMPRODUCT("{1;2;3;4;5;6;7;8;9}")
fails with #VALUE!
=SUMPRODUCT(INDIRECT("{1;2;3;4;5;6;7;8;9}"))
fails with #REF!
I want to use a custom vba function to return the string
eg. something like (if the above actually worked!)
=SUMPRODUCT(INDIRECT(GetArray()))
Or perhaps there is a better solution?
Thanks