Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External References in Functions
I'm having trouble with an external reference in one of my Excel Functions.
The general form of my function is: =COUNTIF( external reference to a range, a value) When I open my worksheet and tell it to update the data, the cells with this formula indicate that there is an error with my function. But then, when I open the workbook containing the external reference, my formulas miraculously correct themselves. Just some additional info. -- I have other formulas that work which reference the same external data. If anybody could give me a clue to what might be going on, I'd surely appriciate it. I've pasted the actual formulas that I've used in case anybody is willing to take a close look at what I've done :). Thanks in advance, -Greg Formula that doesn't work: =COUNTIF('E:\ResearchData_Math90\AlteredDataAndSpr eadsheets\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$D$2:$D$416, B3) Formula That Works: {=SUM( IF( (NOT('E:\ResearchData_Math90\AlteredDataAndSpreads heets\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$B$2:$B$416=5925) ) * ('E:\ResearchData_Math90\AlteredDataAndSpreadsheet s\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$D$2:$D$416=B3),1 ,0))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External References in Functions
Ferencko wrote...
I'm having trouble with an external reference in one of my Excel Functions. The general form of my function is: =COUNTIF( external reference to a range, a value) This is a FORMULA that calls the COUNTIF function. When I open my worksheet and tell it to update the data, the cells with this formula indicate that there is an error with my function. But then, when I open the workbook containing the external reference, my formulas miraculously correct themselves. .... That's because COUNTIF requires range references as first argument, and ranges as instantiated Excel objects can only exist in OPEN workbooks. Excel evaluates external references into closed workbooks as arrays. That means when the other workbook is open, Excel can pass COUNTIF a reference to that range; but when the other workbook is closed, Excel can only pass an array to COUNTIF. To COUNTIF, that's a critical difference. If you may be referring to, er, blocks of cells in closed workbooks, you need to use SUMPRODUCT rather than COUNTIF. Using your example above, change your formulas to =SUMPRODUCT(--( external reference to a range = a value )) This has been a known problem with COUNTIF (and SUMIF) for years. There's no work-around using COUNTIF. You have to use a different function when referring to possibly closed workbooks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External References in Functions
Thanks. This helps.
-Greg "Harlan Grove" wrote: Ferencko wrote... I'm having trouble with an external reference in one of my Excel Functions. The general form of my function is: =COUNTIF( external reference to a range, a value) This is a FORMULA that calls the COUNTIF function. When I open my worksheet and tell it to update the data, the cells with this formula indicate that there is an error with my function. But then, when I open the workbook containing the external reference, my formulas miraculously correct themselves. .... That's because COUNTIF requires range references as first argument, and ranges as instantiated Excel objects can only exist in OPEN workbooks. Excel evaluates external references into closed workbooks as arrays. That means when the other workbook is open, Excel can pass COUNTIF a reference to that range; but when the other workbook is closed, Excel can only pass an array to COUNTIF. To COUNTIF, that's a critical difference. If you may be referring to, er, blocks of cells in closed workbooks, you need to use SUMPRODUCT rather than COUNTIF. Using your example above, change your formulas to =SUMPRODUCT(--( external reference to a range = a value )) This has been a known problem with COUNTIF (and SUMIF) for years. There's no work-around using COUNTIF. You have to use a different function when referring to possibly closed workbooks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data with External References | Excel Discussion (Misc queries) | |||
References to external XLS files | Excel Discussion (Misc queries) | |||
External References in Excel | Setting up and Configuration of Excel | |||
External References | Excel Discussion (Misc queries) | |||
External References not working right. | Excel Worksheet Functions |