Formula to external tab referenced by local cell

[Iwanow]

Hello everybody!

I'm trying to create a formula that gets value from a cell located on
some other sheet. The issue is as follows: exact location of that
"foreign" cell depends on value of some other cell located on the
localtab. Frankly speaking, the formula should look like that:

'Summary'!$1$(8 + H5)

and:
- "Summary" is name of the foreign tab
- "1" indicates the first column in tab "Summary"
- "H5" is a cell in _local_ tab
- value (8 + H5) should be a row number in the "Summary" tab.

Unfortunately, Excel doesn't accept that formula. Any ideas how to fix
it? Thank you in advance for your help.

 

[Arvi Laanemets]

Hi

At first, your formula is a range reference, and it doesn't have any meaning
by itself. You must use it as a source for some another function, with
returns a real value (SUM, COUNT, etc.).

So, you need something like
=SUM(INDIRECT("'Summary'!$1:$" & SUM(8,H5))