Excel and linking

[piper43]

Hey Folks,
I 'm in some desperate need of some advice I do the pricing for my company and over time have created many excel work books that I have to update thoughout the year and it is insane I do the update then I make the same change to about 10 different sheets so my questions is how do I link them All? ie one master and I'm not an excel guru either. Any help would be appreciated

Jamie

 

[sawedoff]

The following is an excerpt from a class lesson I taught about a year ago. Hope it helps.

To refer to cells or ranges in a different file, an example of the syntax is:
[filename.xls]Sheet1!C10:C25

NOTE: This assumes the other file is open. If not, the entire path must be included in front of the open bracket. For this reason, it is often easier to work with linked spreadsheets if all linked files are opened.
Also, if the name of the other worksheet or workbook contains nonalphabetic characters, you must enclose the name (or the path) within single quotation marks.
To refer to cells or ranges in the same file, an example of the syntax is simply:
Sheet1!C10:C25


Sawedoff

 

[klmorgan]

The simple way to do this would be as follows:

Open your Master sheet

and say the pricing data is in column d rows 10 to 25

Then open the slave sheet

Tile the sheets so that you can see both

Say you want to put the information into the same column and rows on the slave sheets

click on d10 in the slave sheet and enter =

then click on d10 in the master sheet and then press return

This will link the slave d10 to the master

Check that the reference in the slave sheet has not made the cell reference absolute by putting $ infront of the letter and the number, if it has remove the $

then copy d10 in the slave down to d11 to d25 in the slave

save the slave and try it out

when you open the slave again it will ask if you want to refresh the data if you say yes it will do so but if you dont have the master open as well it will be a longer job.

I personally tell users to always say no to update but when I update the master iI run a macro to open and update all the slaves. You can just open each in turn saying yes to update and saving

Once you have tried and proved the first slave you can do this with the other sheets

Regards

Keith

 

[piper43]

Hey folks thanks for the info, Keith I already have started doing you suggestion just becomes cumbersome as the master and the slave are fine but I have many other sheets that are not part of the master yet.

 

[KenWright]

Could really do with some more info on what is in the files, and what the relationship is to the 'master' worksheet. What kind of data is it that you are replicating, and what is the reason for the replication.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------