Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 108
Default OFFSET: Returns error when used to a linked file

Hi,

I'm trying to do a dynamic sum function for a daily production given table.

File A is a summary, with many tabs for each product type. File B contains
the daily production detail.

File A has a sumif function:
* Condition range (first parameter) is located in file B, having the product
type list.
* Condition (second paramente) is provided in file A (product type).
* Sum range (third parameter) is located in file B, is the result of a match
function that looks for a given date, then returns the column number to look
for, and then brings a reference to the whole column (using OFFSET).

The problem I'm experiencing is that the cells in File A shows #REF if the
file B is closed. Is there any parameter in Excel to prevent this? (I dig in
every single option an none of them works). Or, is there any alternative
function to OFFSET to bring a whole column as array having a dynamic
parameter?

Thanks in advance.

--
Arturo Gonzalez
Guadalajara, Mexico
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default OFFSET: Returns error when used to a linked file

Arturo wrote...
....
The problem I'm experiencing is that the cells in File A shows
#REF if the file B is closed. Is there any parameter in Excel to
prevent this? (I dig in every single option an none of them
works). Or, is there any alternative function to OFFSET to bring a
whole column as array having a dynamic parameter?

....

There is no option or parameter than avoids the #REF! error. OFFSET
can return only references to ranges, and ranges usables as such exist
only in open files.

There are likely to be workarounds, but you need to provide your
current formula for others to respond with what would work best. But I
can say that references into closed workbooks are returned as arrays,
and through Excel 2003 arrays can't span entire columns. Do you really
need all rows from 1 to 65536? If you have column/field headings in
row 1, you'd need at most rows 2 to 65536.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Offset() formula with height of 1 returns duplicates? Uhl Excel Worksheet Functions 9 December 13th 06 02:22 AM
Why sometimes excel returns error just because function parameters are linked? Paul134 Excel Worksheet Functions 2 February 28th 06 06:15 PM
Offset() returns reference, first not value (proof) Jim May Excel Discussion (Misc queries) 2 October 22nd 05 04:18 PM
linked objects following and leading returns timlishok Excel Discussion (Misc queries) 0 April 7th 05 08:23 PM
linked objects following and leading returns Tim Lishok Excel Worksheet Functions 0 April 7th 05 08:19 PM


All times are GMT +1. The time now is 05:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"