• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

External Relative Links won't update within a SUMIF formula?

G

gauss

New Member
Hi, I think the subject says it all and I just wanted to confirm if this is indeed the behavior of Excel and if there's a non-VBA workaround.

I am deliberately testing the relative addressing behavior of Excel. I have just four files all in the same subfolder. (located inside a main folder)

Main.xlsx
Link1.xlsx
Link2.xlsx
Link3.xlsx

Main.xlsx is linked to the other three. The links to Link1 and Link2 are simple "=" links. But the link to Link3 uses a SUMIF.

To my surprise when I move the main folder between My Documents and Desktop, without fail the links to Link1 and Link2 automatically adjust, but the link to Link3 always points to C:\My Documents

This is quite frustrating as I thought that as long as I link within the same folder and preserve folder structure, I should be able to move the 'container folder' around and never have to worry about losing links.

Thanks
 
In short, SUMIF can't handle an external reference.

While it will retain the value 'ok' after you create the formula, you can test it's integrity by closing the externally referenced workbook, select the cell with SUMIF, hit F2, and then Enter, and formula will crash. If you need to do a SUMIF, either do the SUMIF in external workbook, and link to that value, or import (several options such as Data query, Formulas, etc.) the data from external book to current book, and then do calculation.
 
Hi Gauss ,

My reading of your question is slightly different ; I'll tell you what I tried , and you can see if it applies.

1. Have 2 files open , let us call them FileA and FileB.

2. In FileA I introduced a SUMIF formula , which referred to FileB.

3. When I closed both files , and reopened FileA , it showed a #VALUE! error ; however , when I opened FileB , it showed the correct value.

4. I transferred FileB to another folder.

5. When I reopened FileA , it showed a #VALUE! error ; however , when I opened FileB , it showed the correct value. The SUMIF formula had correctly adjusted its reference to take the new folder location of FileB into account.

6. After bringing back FileB to its original folder , I transferred both FileA and FileB from their original location into a separate sub-folder ; the SUMIF formula adjusted to the change.

9. I transferred the entire sub-folder containing both FileA and FileB to a new location ; the SUMIF formula again adjusted itself.

Narayan
 
You must log in or register to reply here.
Back
Top