Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: kthrynileen
kthrynileen Author
User level: Level 1
0 points

Absolute Cell Reference in Excel 2008 for Mac

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

Posted on Aug 16, 2010 7:38 PM

Reply
9 replies
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Aug 17, 2010 1:02 AM in response to kthrynileen

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
Reply
User profile for user: Badunit
Badunit
User level: Level 7
25,634 points

Aug 17, 2010 8:38 AM in response to kthrynileen

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.
Reply
User profile for user: kthrynileen
kthrynileen Author
User level: Level 1
0 points

Aug 17, 2010 11:30 AM in response to Badunit

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).
Reply

Absolute Cell Reference in Excel 2008 for Mac

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up