MrExcel Publishing
Your One Stop for Excel Tips & Solutions

linking data through multiple worksheets/workbooks

Posted by Jason on January 07, 2002 8:17 AM

Hey, I hope someone can help me! I need to learn how to link information through different worksheets and workbooks, so that when data is entered into one, it will also be entered into another, if that's possible. Thanks for any help or advice

Posted by Mudface on January 07, 2002 8:23 AM

As an easy starting point, simply Copy the area on a sheet where the data is to be initially entered, go to the sheet (or workbook) where you want the duplicate data to appear and Paste Special- Paste Link.

Posted by jason on January 07, 2002 9:32 AM

Thanks Mudface, that helps, but now, how do I get the change in that cell to carry through to the other "links"? ie-suppose I have a simple sum formula totaling a1 thru a5 in cell a6, I've linked a6 to another worksheet like you said but... if I were to change a number and the total changed, I want it to carry through to the other worksheet/book. Is that possible? If so how, or at least give the the proper terminology to look it up in the help section please

Posted by Chris D on January 07, 2002 1:02 PM

Hi Jason,

so your second spreadsheet shows a value that is determined by the SUM on the first spreadsheet, is that my correct understanding ?

ie first spreadsheet has a list of 100 numbers, SUMMED in cell A105 (say totalling 18,666) and your second spreadsheet has a cell in it which reads A105 from the first spreadsheet and puts that total (18,666) in it ?


Posted by jason on January 07, 2002 2:31 PM

response to chris

chris, yes to your first question... kind of. the second spreadsheet just showed the result of the formula-what I want to do is if I change something on the first sheet and it gives me a different answer, ie 18995, then I want it to post 18995 in the second sheet. I appreciate your help, thanks.

Posted by Chris D on January 07, 2002 10:10 PM

Re: response to chris

this should be quite easy....

you just need to tell your second sheet to post the answer that is in your first sheet, whatever the value is and whenever it's updated

so :

in the cell where you want this answer (let's say it's sheet 2 and cell A1) just type "+" (without the quotation marks) and then use your mouse to click on the cell in the first sheet that houses the sum answer of 18,995 (let's say it was in B1000)

so your second sheet cell A1 should show a value of 18,995 but the formula that arrives at this should say "=+Sheet1!A1000"

If, however, you don't have the 18,995 anywhere on your first sheet in the first place but want the actual number to be determined by a formula on your second sheet, again, it's pretty simple :

in cell A1 on sheet 2, just click on the SUM function in your toolbar (it's the capital E thing) then using the mouse, highlight the range of cells in sheet 1 and hit enter. The resulting formula in sheet 2 cell A1 will look something like this : "=SUM(Sheet1!A1:A999)" and return a value, again, of 18,995

When you change any number in that range in sheet 1, the resultant SUM in sheet 2 cell A1 will be updated automatically

I'm assuming it's a sum you're after : if it's an addition of say 5 cells the theory's the same, just enter "+" in cell A1 sheet 2 then use your mouse to click on the cells you want added together from sheet 1

ie :"=+Sheet1!A7+Sheet1!C16+Sheet1!E11+Sheet1!F22+Sheet1!H8" which adds together 5 particular cells from your first sheet

Try it in a blank workbook and change a few of the scource numbers in your first sheet, you should see the value in your second sheet change accordingly

(Apologies for the delay in responding, I'm on UK time!)

Hope this helps

Posted by Jason on January 08, 2002 6:20 AM

Thanks Chris!

appreciate your help, thanks