• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDIRECT in dynamic arrays

Grigoriy

New Member
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.
 
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.
 
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
 
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).
 
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 <<<
Code:
Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) As Variant
Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function
?
 
Last edited by a moderator:
Also supplying a sample file is the fastest way to get an accurate solution
 
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
 
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.
 
Back
Top