What is this mean?

[jollysalman]

=indirect(address(counta(c4:$c$5),column($c$2)))

 

[Joe MAyo]

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

 

[Magriza]

The way to find out what's going on is to break out the constituent parts of the formulae. Start from within the brackets and work your way out. So in this case you would look at:
=COUNTA(C4,$C$5) and =COLUMN($C$2) first.
Then =ADDRESS("result from COUNTA"<ROW COUNTA from arguement>,"result from COLUMN"<COLUMN from arguement COLUMN>)
And finally =INDIRECT("cell reference from ADDRESS"<CELL from ADDRESS returned reference>)

That should help you understand how each separate formula adds a part to the final answer.

 

[Jonmo1]

Breaking down the formula 1 piece at a time..

=indirect(address(counta(c4:$c$5),column($c$2)))

column($C$2) returns the column # of C2 = 3

=indirect(address(counta(c4:$c$5),3))

counta(c4:$c$5) counts how NONBLANK cells there are in the range c4:c5 = 2

=indirect(address(2,3))

Address(2,3) returns the address of the cell Row 2 Column 3 = $C$2

=indirect($C$2) This is now a cell reference referring to C2, just like
=C2


Hope this helps.

 

[Jonmo1]

However, I'm very confused now...

If you write a formula like this

=INDIRECT(C2)
this formula uses the contents of C2 as a Cell Reference...
You would need to put a text string cell reference in C2, like A1
That formula would then be referring to A1..

BUT, if you write it like in this post

=indirect(address(counta(c4:$c$5),column($c$2)))
And say both C4 and C5 are NOT blank...
That translates down to
=INDIRECT($C$2) as described in my previous post.
This formula now refers to C2 and returns the contents of C2. It doesn't further use the contents of C2 as a Cell reference....

Does anyone understand what I'm asking? And know why?

 

[Magriza]

jonmo1,

I understand. Hadn't evaluated it myself but I see you're right. I can only assume that the address should be ADDRESS(COUNTA(...),C2)?

At least then the user could specify a column number to return. And then wouldn't INDEX make more sense? I don't think it's volatile.

 

[Jonmo1]

Yes, of coarse index makes more sense...But I'm still in the wanting to know how MindSet...

And I just figured out why it does that...
Indirect requires a TEXT string to form a cell reference...

And ADDRESS returns a TEXT String "$C$2"

If you write
=INDRECT($C$2)
IN this case, C2 is a Range Object, not a text string. Since indirect REQUIRES a TEXT STRING to make the cell reference, it uses the contents of C2 to form the text string to use as a cell reference..

If you write
=INDIRECT("$C$2")
Because of the Quotes, "$C$2" is already a Text string so it refers direct to C2..

so
=indirect(address(counta(c4:$c$5),column($c$2))) as in the original post, technically translates down to
=INDIRECT("$C$2") - notice the quotes...

the Address function Returns a TEXT String - Not A Range Object...