Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | >> (show all)   Print Thread
Standard User camieabz
(sensei) Fri 03-Feb-12 17:24:05
Print Post

Excel linking between workbooks


[link to this post]
 
Not something I've done much of over the years. In fact I've avoided it where possible. However, I have a spreadsheet with masses of worksheets, and among them some chart sheets or worksheets with charts (there's a distinction).

I'd like to have the chart sheets and/or the sheets with charts copied to another workbook, for public publishing, obviously keeping back the data sheets.

Ideally, once setup, I would open main workbook, change any data (charts within would change too), then open chart workbook, and have it sync with master workbook, then save both and publish the latter, updated.

2007 btw.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User camieabz
(sensei) Fri 03-Feb-12 17:42:36
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
S'ok.

Worked it out.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User micksharpe
(eat-sleep-adslguide) Fri 03-Feb-12 18:01:54
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
Since you're the forum Excel guru we would all be sweating a bit if you couldn't work it out. wink

______________________________________________________________________________________________
‘Life would be so wonderful if only we knew what to do with it.’ - Greta Garbo
.
It Ought to be Easy | Greasemonkey scripts


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Fri 03-Feb-12 18:08:25
Print Post

Re: Excel linking between workbooks


[re: micksharpe] [link to this post]
 
I am? blush

Yay! laugh

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User martinsq
(learned) Sat 04-Feb-12 02:01:13
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
In reply to a post by camieabz:
I am? blush

Yay! laugh


The one that I can't work out is how to link to another sheet within the same workbook..?
It's should be obvious, but...., not to me frown
Thanks,
Martin
Standard User cheshire_man
(knowledge is power) Sat 04-Feb-12 07:10:17
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
One of the problems IME of linking between workbooks is if you should change in any way the full path of linked-to workbook.

Probably due to me being more disorganised than you and deciding to change a file or folder name smile.

Tony
Anonymous
(Unregistered)Sat 04-Feb-12 07:29:09
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
Why not simply output the Chart Sheets and the Charts on Worksheets to PDF Files?

That way, you totally avoid transferring any data, which appears to be the object of the exercise.
Standard User camieabz
(sensei) Sat 04-Feb-12 11:11:28
Print Post

Re: Excel linking between workbooks


[re: martinsq] [link to this post]
 
In reply to a post by martinsq:
The one that I can't work out is how to link to another sheet within the same workbook..?
It's should be obvious, but...., not to me frown
Thanks,
Martin


Lets say you have a sheet called 'abc' and a sheet called xyz. If you wanted to link to a cell in xyz (e.g. A1), type in a cell:

=xyz!A1

or just type in a cell '=', then navigate to the source sheet, then click on the source cell, then click enter.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User camieabz
(sensei) Sat 04-Feb-12 11:12:26
Print Post

Re: Excel linking between workbooks


[re: cheshire_man] [link to this post]
 
I know. It's a good point. My current project is on the desktop, and if I want to move it to a folder later, I'll have to fart around with that. frown

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User camieabz
(sensei) Sat 04-Feb-12 11:14:15
Print Post

Re: Excel linking between workbooks


[re: Anonymous] [link to this post]
 
Well I want some data in chart publish form, without outputting the raw data. So I don't want to mess around with hiding/unhiding sheets or passwords everytime I make a change. Equally I want all my publish data on the document.

I may output the second workbook to PDF (or HTML).

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User camieabz
(sensei) Sat 04-Feb-12 20:39:54
Print Post

Re: Excel linking between workbooks


[re: cheshire_man] [link to this post]
 
Just moved my master copy to its final file folder home. So had to update all the links in the 'copy' workbook. A little tedious, but not painful. Here's why.

I generally work from 'Desktop', so the path was 'C:Users\camieabz\Desktop'.

I keep a catch all folder on my desktop with all my files within. Can't be bothered with Windows filing systems. So all I had to do was change the path of each linked object to (e.g.)

'C:Users\camieabz\Desktop\files\excel'.

Move the source file to its destination. Open the copied workbook, enable the content and note that it doesn't take as long, as the links are dead. Then goto 'edit / edit links to files', and see a list of the links. Click 'change source', drop in the added path data (e.g. \files\excel), without disturbing the rest, and said link will drop to the bottom of the list, as its path is longer. Repeat until the first file comes back up to the top.

Took about eight minutes to change 79 links.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Anonymous
(Unregistered)Sat 04-Feb-12 20:46:16
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
In reply to a post by camieabz:
In reply to a post by martinsq:
The one that I can't work out is how to link to another sheet within the same workbook..?
It's should be obvious, but...., not to me frown
Thanks,
Martin

Lets say you have a sheet called 'abc' and a sheet called xyz. If you wanted to link to a cell in xyz (e.g. A1), type in a cell:
=xyz!A1
or just type in a cell '=', then navigate to the source sheet, then click on the source cell, then click enter.


Ha ! , who would have thought of exclamation mark !
Thanks,
Martin
Standard User cheshire_man
(knowledge is power) Sat 04-Feb-12 20:47:48
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
I haven't used external referencing for several years. Just dug out an old spreadsheet that used it. It was multiple reference to the same external spreadsheet so changing one reference changes them all.

The only trouble then was that I'd referred to fixed cell references that are now in different places.

Ah well. Should have used INDEX or VLOOKUP, but at the time it didn't matter.

Tony
Standard User 12eason
(eat-sleep-adslguide) Sun 05-Feb-12 03:44:45
Print Post

Re: Excel linking between workbooks


[re: camieabz] [link to this post]
 
I find it difficult to believe that people get paid for trivial [censored] like this.

___________________________________________________________________
           Firenet - V21 - Fast4 - f·2·s - eclipseinternet - entanet - aaisp.net - plusnet
Moderator billford
(moderator) Sun 05-Feb-12 11:12:00
Print Post

Re: Excel linking between workbooks


[re: 12eason] [link to this post]
 
I find it difficult to believe that somebody your age persists with this standard of posting.

Perhaps it will improve when you reach double figures.

In the meantime, desist.

~~~~~~~~~~~~
Bill

[email protected] __________________Planes and Boats and ... __________________BQM
The author of the above post is a thinkbroadband moderator but it does not constitute an official statement on behalf of thinkbroadband.
Pages in this thread: 1 | 2 | >> (show all)   Print Thread

Jump to