# #REF! error with Indirect function.



## premjeet (Dec 29, 2022)

Hi,
Can anyone please help with Indirect formula?

=INDIRECT(ADDRESS(4,ROW()+44,,,"Annexure")&":"&ADDRESS(7819,ROW()+44,,,"Annexure"))

Till =ADDRESS expected results comes, but as I try to it club with Indirect formula "#REF!" result.


----------



## RoryA (Dec 29, 2022)

You don't want the sheet name in the second part, so:


```
=INDIRECT(ADDRESS(4,ROW()+44,,,"Annexure")&":"&ADDRESS(7819,ROW()+44))
```


----------



## jdellasala (Dec 29, 2022)

The *#REF!* error means that something in the formula is no longer available. Typically it's a Named Range, or a Table Name - which could have been removed or renamed.
I was able to get rid of the error by removing the *sheet_text* reference in the *ADDRESS* functions. I got this to work: 

```
=INDIRECT(ADDRESS(4,ROW()+44)&":"&ADDRESS(7819,ROW()+44))
```
I looked at the help for ADDRESS, but couldn't get the sheet_text option to work, even using the example in the Help for the function. Don't know what to do about that, but at least you know what's causing it! I did discover something though.
The ADDRESS function will return the Sheet Name even if it doesn't exist. It's just doing a translation of the inputs, so the formula

```
=ADDRESS(4,ROW()+44,,,"Annexure")
```
 returns *Annexure!$AS$4 *even though the Worksheet doesn't exist.


----------



## jdellasala (Dec 29, 2022)

RoryA said:


> You don't want the sheet name in the second part, so:
> 
> 
> ```
> ...


That is SO strange! That it works without the Sheet reference in the second ADDRESS. Nice going!


----------



## RoryA (Dec 29, 2022)

If you were creating a multiple cell range in a formula you would typically use =Sheet1!A1:A100 not =Sheet1!A1:Sheet1!A100, although the latter will actually work. Apparently INDIRECT is a little fussier about syntax!


----------



## jdellasala (Dec 29, 2022)

RoryA said:


> If you were creating a multiple cell range in a formula you would typically use =Sheet1!A1:A100 not =Sheet1!A1:Sheet1!A100, although the latter will actually work. Apparently INDIRECT is a little fussier about syntax!


Of course! Duhhh!!


----------



## premjeet (Dec 29, 2022)

Thanks, @RoryA , It helped me a lot.
Indirect function is evil, and it's odd that it doesn't work with the sheet name in the second part.

Thanks again.


----------

