What does =Indirect(1:3) returns?

[ORoxo]

Don't quite understand what does this function will return. Every value in columns A to C?

Thanks

 

[RoryA]

Most likely that would return an error unless the cells in rows 1:3 contain cell addresses.

 

[ORoxo]

Most likely that would return an error unless the cells in rows 1:3 contain cell addresses.

If you don't have anything in the sheet returns 0. If that helps...

 

[RoryA]

If you don't have anything in the sheet returns 0. If that helps...

It's your question, so you should know if that helps.

 

[Joe4]

Don't quite understand what does this function will return. Every value in columns A to C?

You should be able to run a series of tests with different values, and see what it returns.

No matter what I have in any of the cells, I get a #VALUE error.

Where are you seeing this used? If it is part of larger code, it would be helpful to see that code and know what data you have in rows 1:3.

 

[ORoxo]

It's your question, so you should know if that helps.

Honestly, Rory, not at all

You should be able to run a series of tests with different values, and see what it returns.

No matter what I have in any of the cells, I get a #VALUE error.

Where are you seeing this used? If it is part of larger code, it would be helpful to see that code and know what data you have in rows 1:3.

I'm reading Mike Girvin's book, Ctrl + Shift + Enter, and I'm struggling to understand this specific formula.

The way I'm usually able to learn excel is by understanding what each function returns so I can then make sense of more complex formulas such as that one. However, I'm struggling a lot to understand what the INDIRECT("1:"&D3) - in this case INDIRECT (1:3) - would return

PS: Indirect(1:3) alone would have to be inserted by pressing Ctrl+Shift+Enter

EDIT: my bad, would have to be Indirect("1:3")

 

[RoryA]

Aha. You mean INDIRECT("1:3"), not INDIRECT(1:3). It returns a reference to the first three rows in the sheet, which is then passed to the ROW function, so you get an array containing the numbers 1, 2 and 3.

 

[ORoxo]

Aha. You mean INDIRECT("1:3"), not INDIRECT(1:3). It returns a reference to the first three rows in the sheet, which is then passed to the ROW function, so you get an array containing the numbers 1, 2 and 3.

Yeah, my bad, guys. Sorry!

Would it be possible to return columns instead, for instance? If I think about it =Column(indirect("1:3")) would return every column of the excel worksheet, no?

Thanks once again!

 

[Joe4]

PS: Indirect(1:3) alone would have to be inserted by pressing Ctrl+Shift+Enter

You left out two critical details.
- There are quotes around the "1:3"
- This is an array formula entered with CTRL-SHIFT-ENTER (it works very different that formulas entered with just "enter").

These are essential details that you want to be sure not to leave it. It changes the nature of the question entirely!
When asking questions, try not to oversimplify it to the extent that it does not make sense, or we cannot tell what you are trying to do.
Post #6 is how your original question should have been posted, as it contains all the pertinent details.

 

[RoryA]

Would it be possible to return columns instead, for instance? If I think about it =Column(indirect("1:3")) would return every column of the excel worksheet, no?

Yes it's possible - INDIRECT("A:C") - but would be fairly pointless here. The key is just to get an array with the numbers 1,2,3 in it.