Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect question | Excel Discussion (Misc queries) | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
INDIRECT function question | Excel Worksheet Functions |