# 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
End Function
But it does not work with ranges: I can not function IndirectNotVolatile(#A1;B1).

#### Grigoriy

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

Staff member
No CSE

#### 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
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

#### Attachments

• 22.8 KB Views: 2

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

#### Grigoriy

##### New Member
the file is attached hereto.

#### Attachments

• 23.8 KB Views: 3