Getting #REF! error when updating variable data

Question

I am attempting to create a table that will reflect a sum that is calculated from a set number of business days. The data is pulled from an outside source worksheet. When the source worksheet is open, everything seems to work fine. When the source document is closed, the formula works for only the first two "business days" I specify. After that, I get a #REF! error.

Here is the formula I am entering into the display document:

=SUM('C:\Folder[Sourcefile.xlsx]Sheet1'!F4:INDEX('C:\Folder[Sourcefile.xlsx]Sheet1'!F4:F28,A2,1))

Source File:

Example where the formula reflects the correct result:

Example where formula gives me #REF! error:

So, I am trying to produce a sum in the display document that reflects the month-to-date total on a certain day. For instance, on the second business day (I enter 2 in cell A2 of display document, to indicate how many rows to sum from the start cell on the source document, F4), I get the correct sum of 134 (which is F4+F5). However, when I enter 3 or higher in cell A2, I get the error.

What can I do to fix this?

Answer 1

This is not an uncommon problem attempting to link formulas to closed workbooks. I usually advise users to do the following and it removes the headaches.

Instead of linking formulas to closed workbooks you will have more success if you copy the data from the source and use PasteSpecial -> Paste Link into a worksheet in the destination workbook. That way you only have all of the cells linked to the source without the formulas. Then set up your formulas to simply reference the linked worksheet. The linked worksheet can be hidden.

Also I would use the following formula with Offset to reference the range to be summed. Note that this formula will not work at all if referencing a closed workbook but works well on a hidden worksheet that is linked to the source. Name of the hidden linked worksheet is "Link to Source".  Reason for subtracting 1 from A2 is that offset works on the basis of how many times you would need to press the down arrow to get to the offset cell and so if you enter 4 in cell A2 then if you selected F4 then you would need to press the down arrow 3 times to get to the 4th cell.

=SUM('Link To Source'!F4:OFFSET('Link To Source'!F4,A2-1,0,1,1))

Following added after initial posting.

An even simpler formula. See Help for more information on Offset function. The formula works using the height from the initial cell. Note that A2 does not have 1 subtracted from it (as previous formula) because the height is the total height including the first and last row and there is actually no offset to rows or columns; it is simply the reference for a total height of A2 where the reference is the first cell and the height is total rows down.

=SUM(OFFSET('Link To Source'!F4,0,0,A2,1))

Answer 2

I looked everywhere for a solution to my problem that allowed me to use a separate closed workbook as my source, but wasn't able to find anything, so I figured this would be my only option.

Thank you for your answer. The formula works, when I paste-link my data, so I will employ this as my solution. Have a great day!