Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel external workbook references.

Aug 22, 2010 1:37PM PDT

I am using Excel '03 on a WinXP system. I created a spreadsheet with references to cells in another workbook. It seems I have to open the source workbook to get my new sheet to update the data. I thought since I already had the full path ref in my cells, that Excel would be able to just go out to the other file and grab the data without me having to open the other (source) workbook.

Discussion is locked

- Collapse -
Yes that should work
Aug 22, 2010 9:11PM PDT

I've done it often myself.

Initially I will have both workbooks open as it is just easier to highlight a cell in my target workbook, press the = sign, then highlight the cell in the source workbook, and press Enter. This provides the link in the correct format.

Then, I save the source workbook and close it down. That way the link in the target workbook changes to the full path. Then any future changes I make to the source workbook are picked up in the target book, even if the source workbook is closed at the time I have opened the target workbook.

What happens when you try this?

Mark

- Collapse -
Excel external ref
Sep 3, 2010 2:22AM PDT

Thanks for the response. Unfortunately, my formula is rather complex and uses a MATCH function to find the right cell to copy the data from. It is not always the same exact cell that I would be pulling from. I think I have a possible workaround to the "full file path" issue. I am going to copy the source spreadsheets into a "buffer" folder each week so that filepath will always be the same. A bit more effort that I don't think I should have to do but...

- Collapse -
Interesting
Sep 3, 2010 6:08AM PDT

I always found MATCH to be difficult to comprehend, and every time I wanted to use it, I had to re-learn how, Happy

I don't see why MATCH should fail with external reference linking though, although I understand what you mean about the source cell not always being the same cell.

Is there a workaround? What if the result of the MATCH function was always recorded in a set cell elsewhere on the spreadsheet, then the external linking could be made to that set cell?

Mark