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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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. -- Gary''s Student "Peter Bernadyne" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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 -- 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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 -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=515892 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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, but only 0 or 1 depending on whether I use COUNT or COUNTA. Regardless, it doesn't return the proper value, (which, with the direct formula itself is 2689, fyi). Is there something else I might have to do in order to return the correct value instead of 0 or 1, by any chance? Any help is much appreciated. -Pete -- 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=515892 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question on INDIRECT
Finally!!! It looks like I've got it and the trouble I was having was with the quotes, indeed. Rather than using several double quotes (") (as the escape character for itself during regular concatenations) it would appear that this 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, -Pete -- 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 |
Reply |
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 |