Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
INDIRECT.EXT, from Laurent Longre's Morefunc 4.2 Add-in, is giving me a
"#VALUE!" error message in the cell with the formula: =SUM(INDIRECT.EXT("[MyBook.xls]MySheet!$A$1:$A$3")) when MyBook.xls is closed; it only works when I open MyBook.xls. From what I've seen in the help file and other posts in this forum, it is supposed to work for closed workbooks and for ranges of cells. How can I get it to work? Perhaps there is an update, or a way of using this function that I'm not aware of? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
On Sun, 17 Dec 2006 06:37:00 -0800, hmm wrote:
INDIRECT.EXT, from Laurent Longre's Morefunc 4.2 Add-in, is giving me a "#VALUE!" error message in the cell with the formula: =SUM(INDIRECT.EXT("[MyBook.xls]MySheet!$A$1:$A$3")) when MyBook.xls is closed; it only works when I open MyBook.xls. From what I've seen in the help file and other posts in this forum, it is supposed to work for closed workbooks and for ranges of cells. How can I get it to work? Perhaps there is an update, or a way of using this function that I'm not aware of? Thanks. I believe the problem is your syntax. You included the function required double quotes, but omitted the "single quotes" that are part of the Excel required naming convention. =SUM(INDIRECT.EXT("'[MyBook.xls]MySheet'!$A$1:$A$3")) Also, although you did not mention it one way or the other, I believe the formula you posted will execute faster as an array formula. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
Thanks Ron.
I tried again, this time checking carefully all quotes. Suppose cells A1 through A10 the numbers 1 to 10. The formula =SUM(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10")) will indeed return the sum of all 10 cells (55), even when MyBook.xls is closed. However, suppose I only want a subrange of 3 cells beginning with A2. The formula =SUM(OFFSET(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10"),1,0,3)) only works (gives the correct result of 9) when MyBook.xls is open. When MyBook.xls is closed, I get the "#VALUE!" error (Since the limits are calculated in another formula, it is not possible to enter the subrange explicitly as "A2:A4".) Any other ideas? "Ron Rosenfeld" wrote: On Sun, 17 Dec 2006 06:37:00 -0800, hmm wrote: INDIRECT.EXT, from Laurent Longre's Morefunc 4.2 Add-in, is giving me a "#VALUE!" error message in the cell with the formula: =SUM(INDIRECT.EXT("[MyBook.xls]MySheet!$A$1:$A$3")) when MyBook.xls is closed; it only works when I open MyBook.xls. From what I've seen in the help file and other posts in this forum, it is supposed to work for closed workbooks and for ranges of cells. How can I get it to work? Perhaps there is an update, or a way of using this function that I'm not aware of? Thanks. I believe the problem is your syntax. You included the function required double quotes, but omitted the "single quotes" that are part of the Excel required naming convention. =SUM(INDIRECT.EXT("'[MyBook.xls]MySheet'!$A$1:$A$3")) Also, although you did not mention it one way or the other, I believe the formula you posted will execute faster as an array formula. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
On Sun, 17 Dec 2006 08:29:00 -0800, hmm wrote:
Thanks Ron. I tried again, this time checking carefully all quotes. Suppose cells A1 through A10 the numbers 1 to 10. The formula =SUM(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10")) will indeed return the sum of all 10 cells (55), even when MyBook.xls is closed. However, suppose I only want a subrange of 3 cells beginning with A2. The formula =SUM(OFFSET(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10"),1,0,3)) only works (gives the correct result of 9) when MyBook.xls is open. When MyBook.xls is closed, I get the "#VALUE!" error (Since the limits are calculated in another formula, it is not possible to enter the subrange explicitly as "A2:A4".) Any other ideas? Well, the problem seems to be that, when the workbook is closed, INDIRECT.EXT (as described in the HELP section), returns the "VALUES" of those cells. These values are returned as an array. The OFFSET function, on the other hand, requires that the first argument be a cell reference, not an array of values. Hence the VALUE error. It is interesting that if the workbook is open, INDIRECT.EXT returns the cell reference. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
Thanks, Ron, for all your great help.
If you (or anyone else) has any other ideas how to achieve the same result, I welcome them. "Ron Rosenfeld" wrote: On Sun, 17 Dec 2006 08:29:00 -0800, hmm wrote: Thanks Ron. I tried again, this time checking carefully all quotes. Suppose cells A1 through A10 the numbers 1 to 10. The formula =SUM(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10")) will indeed return the sum of all 10 cells (55), even when MyBook.xls is closed. However, suppose I only want a subrange of 3 cells beginning with A2. The formula =SUM(OFFSET(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10"),1,0,3)) only works (gives the correct result of 9) when MyBook.xls is open. When MyBook.xls is closed, I get the "#VALUE!" error (Since the limits are calculated in another formula, it is not possible to enter the subrange explicitly as "A2:A4".) Any other ideas? Well, the problem seems to be that, when the workbook is closed, INDIRECT.EXT (as described in the HELP section), returns the "VALUES" of those cells. These values are returned as an array. The OFFSET function, on the other hand, requires that the first argument be a cell reference, not an array of values. Hence the VALUE error. It is interesting that if the workbook is open, INDIRECT.EXT returns the cell reference. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT.EXT with vector reference
On Mon, 18 Dec 2006 00:06:01 -0800, hmm wrote:
Thanks, Ron, for all your great help. If you (or anyone else) has any other ideas how to achieve the same result, I welcome them. The problem, if I understand what you are doing, is to return an array of values from an array of values. One way to do that would be with the LOOKUP function. In your case, assume the text form of the address is in A1. =INDIRECT.EXT(A1) would then return an array containing the values in the range specified. Let us say you want to sum the 2nd, 3rd and 4th items in that array. A formula of the sort: =SUM(LOOKUP({2,3,4},ROW(INDIRECT("1:"& COUNT(INDIRECT.EXT(A1)))),INDIRECT.EXT(A1))) would sum the 2nd, 3rd and 4th items returned by INDIRECT.EXT(a1). This is the vector form of the LOOKUP function. I believe it can be entered either normally or as an array formula. For speed, Longre advises using the array method of entry. Without knowing more about your specifications, it's hard to advise you how to compute the array {2,3,4}, but some variant of the ROW(INDIRECT(...)) method should work. e.g. =ROW(INDIRECT(start & num_of_entries)) instead of the computation you are using for the OFFSET function. There may be simpler methods of doing this. I have not searched the NG for it, though. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT.EXT reference | Excel Discussion (Misc queries) | |||
INDIRECT.EXT Help Required | Excel Worksheet Functions | |||
Error using Excel add-in with INDIRECT.EXT function | Excel Discussion (Misc queries) | |||
INDIRECT.EXT help needed | Excel Worksheet Functions | |||
INDIRECT.EXT question | Excel Worksheet Functions |