Ask Your Question
0

Having some difficulty with linking cells between worksheets

asked 2020-08-22 19:44:05 +0200

CHEECHBURGER gravatar image

I have 5 worksheets each with several tabs. I use "=" then with the mouse point to the cell I wish lined. The data goes in and I save it. On the next day, I make changes to the linked sheets and they do not update other sheets linked to the cells. I have added all the sheets in the Security screen as trusted locations Recalculate automatically is set and protections are off and calculate automatically for trusted sites is on.

but when i SAVE THE spreadsheet and open it again on the next day the data does not automatically update to changes I have made. and i get REF or some other error - no code.

any ideas?

edit retag flag offensive close merge delete

Comments

Can you show us the references to other files (the formulas, when working)? At best attach two linked files to your question(edit the question), but if that is not possible, copy the formula and paste it into a comment here.

keme gravatar imagekeme ( 2020-08-22 20:05:40 +0200 )edit

I make changes to the linked sheets and they do not update other sheets linked to the cells

This sounds like you are expecting that a change in a document being referenced by another document's cell somehow pushes the change to the document, having a link to the current document. That's definitely wrong. Linking to other document cells is a pure pull mechanism, which requires the linking document to be opened to get any update.

Regarding #REF! error: I can't imagine that these error got anything to do with changing an external document, since references across documents are always absolute and even if you delete the row containing a cell (e.g row 1) which is referenced by another sheet (e.g A1), you get a new cell being A1.

Opaque gravatar imageOpaque ( 2020-08-22 20:20:33 +0200 )edit

These are two links between the same two worksheets. the formulae in other situations are similar, it happens in all of the worksheets, the same way. I open all of the worksheets one at a time and all are opened when I start to add data, expecting to see changes.Again recalulate is on. These worksheets were constructed in exel and worked in excel for a couple of years. and these links between the sheets are the same. The sheets are quite large and contain sensitive information. So I copied the formulae below for two of the many links between the sheets.

link one RECEIVING CELL ='file:///C:/Users/ralph/Desktop/INVESTMENTS - SCHEDULE D.xlsx'#$'INS WKS 20'.E48

SENDING CELL – THIS NUMBER IS A SUM AND UPDATES FROM CELLS ABOVE AND THE SUM CHANGES CORRECTLY $959

link two RECEIVING CELL ='file:///C:/Users/ralph/Desktop/TAX ESTIMATES - 2017 N ...(plus)

CHEECHBURGER gravatar imageCHEECHBURGER ( 2020-08-22 22:29:34 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-08-29 12:14:14 +0200

CHEECHBURGER gravatar image

={'file:///C:/Users/ralph/Documents/LIBRE OFFICE TAX FILES/BEYOND INT DIV.xlsx'#$'2020 INT DIV'.AB22}

Tried DDE and this is what happens. none of the instructions about linking cells in two different workbooks work. Macro security trusted is there this whole thing stinks.

edit flag offensive delete link more
0

answered 2020-08-22 21:20:28 +0200

GrahamLees gravatar image

To have persistent updating of data between two spreadsheets, insert Dynamic Data Exchange (DDE) links:

Select and copy the range of cells to be linked to.
Change to the spreadsheet that requires the link and select the place where you want the link to be. (text on the wiki is not complete; reference for the edit)
Open the Paste Special dialog, Edit > Paste Special and check the Link option (the checkbox at the bottom left of the dialog).
Click OK to insert the DDE link.

These cells will have a persistent link to the contents of the other sheet and the values returned can then be used in other formulas.

edit flag offensive delete link more

Comments

thanks, i have put this DDE in link one I referenced above. When I check the Libre documentation Chapter 10 page 24 it indicates I should see {=dde at the beginning of the formula. I only see { } at beginning /end, no "DDE" - the data is not changed when i make additions to the sending cell values. IF I put a formula in an empty cell, {DDE, the cell reference i want} Err 512 is returned can you play detective and ID what i messed up yet again

CHEECHBURGER gravatar imageCHEECHBURGER ( 2020-08-23 11:42:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-22 19:44:05 +0200

Seen: 85 times

Last updated: Aug 29 '20