• 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.
 

pecoflyer

Active Member
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

Excel Ninja
Staff member
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

New Member
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

New Member
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:

Hui

Excel Ninja
Staff member
Also supplying a sample file is the fastest way to get an accurate solution
 

Hui

Excel Ninja
Staff member
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

New Member
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.
 
Top