INDIRECT in dynamic arrays

[Grigoriy]

Hello everybody.
Why =INDIRECT(H2#&"!$A$1") does not go, provided that H2# is an array of 1:3 which are names of the sheets?
Obliged if you could possibly provide an answer.

 

[pecoflyer]

Syntax

INDIRECT(ref_text, [a1])

The INDIRECT function syntax has the following arguments:

• Ref_text Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

 

[Hui]

You may want to try
=Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter
or
=Sumproduct(INDIRECT(H2#&"!$A$1") )

Also supplying a sample file is the fastest way to get an accurate solution

 

[Grigoriy]

Hello Hui,
Great many thanks for the reply.
Could you possibly help on udf to replace indirect function?
I have found this so far:
Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) as Variant
Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function
But it does not work with ranges: I can not function IndirectNotVolatile(#A1;B1).

 

[Grigoriy]

Any way around without CSE?
=Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter

 

[Hui]

Use Sumproduct instead of Sum
No CSE

 

[Grigoriy]

Hello, Hui!
- Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter - OK,
- Sumproduct(INDIRECT(H2#&"!$A$1") ) gives zero
- Sum(INDIRECT(H2#&"!$A$1") ) sums in one cell
What would work faster Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter or:
>>> use code - tags <<<

Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) As Variant
Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function

?

 

[Hui]

Also supplying a sample file is the fastest way to get an accurate solution

 

[Grigoriy]

Please, view the following file.

 

[Hui]

I think this does what you want ?
=SUM(INDIRECT("'"& SEQUENCE(A5,A1) & "'" & "!A1")) Ctrl+Enter
If you wanted the whole column
=SUM(INDIRECT("'"& SEQUENCE(A5,A1) & "'" & "!A1:A5")) Ctrl+Enter

 

[Grigoriy]

Thanks a lot for the reply.
It is closer but still not there...

The H column does not spill...
The spill is to be the following:
Sum A1's from sheets '1:5'
Sum A2's from sheets '1:5'
and so on...
the file is attached hereto.

 

[Grigoriy]

the file is attached hereto.