9 Replies Latest reply: Sep 3, 2010 9:32 AM by kthrynileen
kthrynileen Level 1 Level 1
I am trying to link data between different workbooks. The only thing I was able to find about this in the forums is that it definitely will not work in Numbers - how about Excel?

I have tried both absolute and non-absolute references. Regardless, when I insert a row in the source sheet, all linked data on the destination sheet that comes after the row insertion is incorrect. The reference does not move with the insertion. It DOES work when the sheet I'm referencing is contained in the same workbook but not with separate workbooks.

Is this a PC vs. Mac thing?

I apologize if this has been answered ad nauseum, my search skills did not turn up anything remotely close to what I am looking for.

MacBook, Mac OS X (10.6.2), Excel 2008 for Mac
Reply by Badunit on Aug 17, 2010 8:38 AM Helpful
My test on Excel 2008 was this:

Source sheet, column A = 1,2,3,4 in consecutive rows
Destination sheet A1 =[Workbook1]Sheet1!$A1 and I filled this down a few rows so I had references to $A1, $A2, $A3, $A4, etc. Note: "Workbook" has square brackets around it in the formula but these won't show up in the post.

I then inserted a row in the source sheet between rows 1 and 2. The references in the destination sheet updated to A1, A3, A4, A5 as I would have expected them to do and the same behavior that you would get if the references were to cells in the same workbook versus an external workbook. Absolute versus relative references does not change that. It sounds like you are seeing something different.

I did the above test with both sheets open but it also worked when I edited, saved, and closed the source sheet then re-opened the destination sheet and chose to have Excel update the links.

All replies

  • Barry Level 7 Level 7
    kthrynileen wrote:
    I apologize if this has been answered ad nauseum, my search skills did not turn up anything remotely close to what I am looking for.


    Hi Kathryn,

    To my knowledge, it hasn't been answered at all here, Not surprising, though, as Apple Discussions is intended for discussion of Apple's software (and hardware), and the Numbers '09 forum is specifically for discussion of issues relating to Numbers.

    Numbers does not support linking to external documents, with two exceptions: A hyperlink in a numbers cell can be used to open a webpage into a browser, and to open and address a new email message in an email client.

    For the answer to your MS Excel question, I'd suggest visiting the Excel forum at Microsoft's Mactopia site. The first link will take you directly to the forum, the second to the Mactopia from page.

    Regards,
    Barry
  • kthrynileen Level 1 Level 1
    Thanks Barry - I've posted the question there as well! It's been a little frustrating as this is something I used to be able to do in the PC version but I know that not all of the features transferred to the Mac version.

    If I find an answer in the other forum, I will link it here in case others are looking.
  • Jerrold Green1 Level 7 Level 7
    Withdrawn...

    Jerry

    Message was edited by: Jerrold Green1
  • Badunit Level 6 Level 6
    expertise.itunes
    iTunes
    My test on Excel 2008 was this:

    Source sheet, column A = 1,2,3,4 in consecutive rows
    Destination sheet A1 =[Workbook1]Sheet1!$A1 and I filled this down a few rows so I had references to $A1, $A2, $A3, $A4, etc. Note: "Workbook" has square brackets around it in the formula but these won't show up in the post.

    I then inserted a row in the source sheet between rows 1 and 2. The references in the destination sheet updated to A1, A3, A4, A5 as I would have expected them to do and the same behavior that you would get if the references were to cells in the same workbook versus an external workbook. Absolute versus relative references does not change that. It sounds like you are seeing something different.

    I did the above test with both sheets open but it also worked when I edited, saved, and closed the source sheet then re-opened the destination sheet and chose to have Excel update the links.
  • kthrynileen Level 1 Level 1
    Oddly enough, that test worked for me as well when I tried it last week but then yesterday I opened the destination workbook - everything was fine - then opened the source workbook and added a few rows, the destination sheet was jumbled again. I think my solution is that I'll need to use vlookup - was trying to avoid as it seemed like more than what what was necessary but now it is looking like it is the only way to do this. Will post results here after I convert everything (that's the main problem - it is a LOT of data that needs to be converted into formulas).
  • Level 8 Level 8
    May you be fair enough to use the Numbers vocabulary.

    In Numbers there is no workbook.
    You use the word Sheet for what resemble to a table.

    It's really boring to have to translate questions in the correct wording.

    Yvan KOENIG (VALLAURIS, France) mardi 17 août 2010 20:45:30
  • kthrynileen Level 1 Level 1
    I cannot use Numbers - too many differences when everyone in my company is using Excel. Also, I have some pretty complicated spreadsheets that I don't think would translate well in Numbers.

    In the end, I used vlookup to find the data I needed in the separate workbook and that resolved my issue entirely.
  • Badunit Level 6 Level 6
    expertise.itunes
    iTunes
    I'm glad you got it resolved. Maybe it is an Excel bug but I didn't find anything on the web about this problem (it was not an extensive search, though).
  • kthrynileen Level 1 Level 1
    Vlookup solved the problem.