Question on INDIRECT

[Peter Bernadyne]

I am trying to construct a reference for a name in my workbook which is
intended to be dynamic in nature and will be used in charting. Because
of the dynamic nature of the data, I need to create a dynamic range but
one which is itslef dependent on user inputs. Basically, I have one
line chart on a summary worksheet which I want to allow the user to
modify to chart the ranges of data on various worksheets.

Variables in my formula are as follows:

wksheet
....takes on the value a user decides from a drop-down box and is a
valid reference to one of my worksheets in the workbook (it's
correct).
[So options are Sheet1, Sheet2, etc. although these are renamed in my
workbook to their functional names]

refcol
...column letter which changes depending on user input from a dropdown
box. I need this because there are various types of data per worksheet
and I'd like the user to be able to view these, as well.
[Options here are A, M, etc., all column letters]

As a first step, I need to calculate the range length of the resulting
choices the user has inputted. This is where I am having trouble.
The straightforward formula that would give me this length is:

COUNT(Sheet1!A:A)...which works just fine.

As I need to vary this result, however, I am using the variables above
in a concatenation along with INDIRECT but only coming up with a #REF!
error.

The concatenation formula is as follows:

CONCATENATE("COUNT(",wksheet,"!",refcol,":",refcol,")")

which gives me exactly the formula above, as in: COUNT(Sheet1!A:A)

However, when I try:

INDIRECT(CONCATENATE("COUNT(",wksheet,"!",refcol,":",refcol,")"))

I only get the #REF! error. Can anyone help me out with this, please?

Any help is much appreciated.

 

[Guest]

Here is an easy way to de-bug INDIRECT(). INDIRECT() wants a text string
that looks like a reference.

=INDIRECT(A1) will fail
=INDIRECT("A1") will display the contents of A1
=INDIRECT("Sheet2!A1") will display the contents of A1 in the second sheet
=INDIRECT(C1&D1) will work if C1 contains E and D1 contains 1


So if INDIRECT(something) returns an error, enter =something in an un-used
cell and verify it "looks" like a reference.

 

[Peter Bernadyne]

Hi,

Thanks for that reply.

I've tried many variations of what appear to me to be references that
INDIRECT should accept, but I can't seem to get them to work.

Specifically, I do get INDIRECT to work for 1 cell reference, as in:

INDIRECT("Sheet2!A1")

However, when it comes to trying to use COUNT, no variation I use seems
to work and all give me #REF!

Do you think you could get INDIRECT above to work if it were COUNT(A:A)
instead of merely the cell reference A1?

Any ideas on that would be much appreciated - not sure why the token
hasn't fallen through, yet(!)

Cheers,

-Peter

 

[Cutter]

Without having gone deeply into your situation I think you need
something like:

=COUNT(INDIRECT(CONCATENATE("your formula")))

"your formula" is without the COUNT argument you're currently trying

You're currently putting the COUNT inside the INDIRECT, it needs to be
the other way around

so something like:
=COUNT(INDIRECT(CONCATENATE(A1,"!",A3,":",A3)))
where A1 contains your sheet name and A3 contains your refcol letter

 

[Peter Bernadyne]

Hi,

Thank you very much for this suggestion and your valuable insight.

Indeed, I am convinced that this is part of the problem.

However, when I try this, the formula does indeed return a value, bu
only 0 or 1 depending on whether I use COUNT or COUNTA. Regardless, i
doesn't return the proper value, (which, with the direct formula itsel
is 2689, fyi).

Is there something else I might have to do in order to return th
correct value instead of 0 or 1, by any chance?

Any help is much appreciated.

-Pet

 

[Dav]

It works on my sheet! however if you highlight the
=COUNT(INDIRECT(CONCATENATE(A1,"!",A3,":",A3))) bit of the formula and
press F9 does it resemble the directly input Formula?

is should look something like =COUNT(INDIRECT("sheet1!a:a"))

all be it being inside " " as it is an indirect formula if Sheet 1 is
sheet1 for example this would cause a problem and return a 1 with
counta and 0 with count. So this is what I suspect you have done

regards

Dav

 

[Peter Bernadyne]

Finally!!!

It looks like I've got it and the trouble I was having was with th
quotes, indeed.

Rather than using several double quotes (") (as the escape characte
for itself during regular concatenations) it would appear that thi
formula requires one use only 2 (one escape, one quote charcter).

The working formula would at long last appear to be:

=COUNT(INDIRECT(CONCATENATE("",wksheet,"!",refcol,":",refcol,"")))

Many many thanks go out to all those who helped me out with this one.

I really appreciate all your help.

Cheers,

-Pet