# What is this mean?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Joe MAyo

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

##### Well-known Member
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>)

#### Jonmo1

##### MrExcel MVP
Breaking down the formula 1 piece at a time..

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

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

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

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

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

##### Well-known Member
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

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

Last edited:

Replies
3
Views
548
Replies
3
Views
299
Replies
7
Views
2K
Replies
6
Views
455
Replies
1
Views
375

1,190,652
Messages
5,982,124
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back