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.


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=515892