• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

use indirect to refer to named range in an excel formula.

Q

questio

New Member
Good evening!


I have been trying to understand why the following formula does not work as I have used it successfully for named ranges, and it is the only way I know to refer to a range name where teh name is in another cell.


in A, B, C, I have ranges that are dynamic and are called AA, BB and CC

Range AA is assigned using the formula = offset($A$2,0,0,count($A:$A),1)

Range BB and CC the same way.

In column V, from V1 to V3, I have entered manually the names, AA,BB and CC without quotes.

In column W1 to W3 I have the following formulas

W1=count(indirect(V1))

W2=count(indirect(V2))

W3=count(indirect(V3))


The results are stubbornly remaining = 0

When I use the "evaluate formula", the indirect(V1) becomes indirect("AA") then indirect(#REF).

The entries AA, BB and CC where done manually and the format is general.

The only reason I can see that it does not work is that they are dynamic ranges... though it does not make sense to me! :)

When I write simply count(AA), I get the right result, so "count" can accept dynamic range names.

Help!

Thanks!

Danièle
 
Questio


Firstly, Welcome to the Chandoo.org forums.


Change your formula to
Code: 
=Counta(Indirect(V1))
 
Hi Daniele ,


You are right. The problem appears to be a use of INDIRECT with a dynamic range ; using INDIRECT with an static range creates no problem.


I tried using an absolute address for the range AA , and the formula =COUNT(INDIRECT(V1)) where V1 contains AA , and where AA is defined as a range , say A2:A9 , returns the correct value , assuming that the range A2:A9 contains numeric data.


The moment AA is made dynamic by using either INDEX or OFFSET , the formula returns 0.


Narayan
 
THANK YOU!


Sorry for the delay in replying! I could get the answer from the forum at my work place but could not reply as I forgot my password! It was on my computer at home which had a few glitches but as you can see is back working.

First and foremost, Thanks!


Hui, your answer "hung up"; it does not work with dynamic ranges, and I wish I knew why! :)


Narayan made me feel better by letting me know what I had suspected was correct and it was the dynamic ranges that messed things up!


Haseeb resolved the issue! I am just a bit curious as to why your formula works, and not the others?


I am now really enjoying using dynamic ranges which save so much time, but I obviously am hoping too much from them and was wondering whether there is some rule that would help me decide which formula to use with indirect and dynamic ranges?

Thank you so much!

PS is there a way to change the original password to a more manageable one? I do feel very annoyed and ashamed with myself to have taken so long to thank you for helping me out!


I wish you a great day,


Danièle
 
Questio

Click on your name under your avatar or at the very top of the page

On the screen that opens there is an Edit Tab Upper Right of screen under the Google Search Box

On the Edit screen change your password
 
Thanks Hui! That will allow me to follow all everywhere rather than cram my brains with very strange passwords...

Thanks!
 
You must log in or register to reply here.
Back
Top