Linking a spreadsheet 1

[cbearden]

I have a spreadsheet that i need to make a copy of and then link that copy to the original so when someone goes into the copy, they see the updated information as well as being able to enter information and i can see the information that they entered(from the copy) in the original.

Thanks

 

[GlennUK]

Am I understanding this correctly ( sounds like circular logic to me )? You want the copy to get information from the original, AND you want the original to get the same information from the copy ( if it has been overtyped )?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[doxiepup]

cbearden:
Have you considered using the "track changes" feature?

 

[cbearden]

yes, that is what i meant.

I type in

col A , Col b, col c
How , are, you

I save the file. If someone opens the other file(the copy), i wnat them to be able to see the changes.

What is the track changes feature?

 

[GlennUK]

cbearden: that's only half of what you first said.

If this is all you want, then in the copy enter formulae that link to the original. Have both copies open when creating the formulae, to make the process easier, and in the copy type = and switch windows to the orginal and select the required cell and press Enter, and you've created a link.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cbearden]

half? what do you mean?
if you mean changes made from the copy to show up in the original, then yes, that too.

 

[GlennUK]

Hi cbearden,

half? Yes, you've now confirmed that you want the information to go in BOTH directions.

To come up with a workable solution you need to specify the rules that this data flow is supposed to follow.

You've said that you want the person using the copy to see updated information, AND that changes they make be seen in the original. So, when information has been changed in both, which is the information that should be used? The updated original, or the overtyped information in the copy?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cbearden]

Changes in the original needs to be seen in both files. There will be no overtyped information in the copy, only new rows(requests).

 

[GlennUK]

So, what happens with the new rows(requests)?

Are they then inserted into the original?

Is the new copy to be distributed afterwards?

Am I right in thinking there is only one copy involved in this process?

Have you actually tried playing with the solution I first posted ( formulae to peform external linking between sheets )? As this can be used to link in both directions ( have the links going from the original to the copy for existing rows, and have the links going from the copy to the original for those rows that are supposed to be blank ).



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cbearden]

Whatever is done in the original needs to be done in the copy.

So if i go into the original and add some rows, then i want the copy to show the same thing, just as if i went to both files and did the same thing.

 

[KenWright]

Have you considered the option suggested by doxiepup of using Track Changes. I'm struggling to see how else you will achieve what you are after, even if you use code. You are looking to achieve database functionality in a spreadsheet because you effectively want multiple users to be able to edit the file at the same time, even though you have two files.

Even with code you would have to have it so that any changes made to one are either made at the same time to the other book, in which case you may as well have just one book, or at a later date in which case you would need code that stores all those changes and then at a time when the other book is not opened by anybody it goes in and makes those changes.

Problem is that in the meantime someone may have made changes to say the second book and the code with changes from the first book then screws up those changes because it wasn't expecting them.

I'm assuming you perhaps have different drives or servers with different people having access but you want a common file sitting on each so they can all see it, but to be honest I'm just thinking you are maybe on a hiding to nothing with this approach.

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

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

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

 

[xlbo]

one word........
MSAccess

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[cbearden]

Not everyone here has access to Access.

I have one file that I copied a wksheet to another file. Some how, everytime i open the 2nd file, it asks to update. how i did it, i don't know. that is something like what i'm looking for.
what is track changes?

 

[xlbo]

There is a difference between linking files (what GlennUK has discussed) and the database functionality you want to employ.
If you need a database, you need to go and procure some database software - either that, or you'll need to learn how to a very proficient VBA programmer very quickly.

Sorry, but that is how it is. If you need database functionality, someone, somewhere along the line, needs to stump up the cash to acquire the licence...

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[doxiepup]

cbearden:

Try this and see if it's what you want:

Open your Excel file, then click on TOOLS from the menu, then choose SHARE WORKBOOK, then click on the EDITING tab and put a check by "allow changes by more than one user at the same time. This also allows workbook merging."

By checking this feature you will be able to have the SAME file opened by multiple people, but yet still be able to see then changes by everyone.


If you then want to see a summary of all the changes, then after changes have been made, click on TOOLS, then TRACK CHANGES, then HIGHLIGHT CHANGES. This will show you changes highlighted on the screen, with each person who made changes highlighted with a different color.

Also...you may also check LIST CHANGES ON A NEW SHEET when you choose HIGHLIGHT CHANGES. This will list all the changes on a new worksheet. Please note--this new worksheet (which will be called HISTORY) is temporary, and will disappear when you close the file, even if you save it. (You can, however, recreate it by clicking on TOOLS, then TRACK CHANGES, then HIGHLIGHT CHANGES again.

 

[pkhoo]

offset problems....
I have a offset formula which references another spreadsheet which works fine when the referenced spreadsheet is open but gives a value# message when it is not????? Please help... thank you.

running excel 2000
regards

 

[xlbo]

pkhoo - start a new thread - do not piggyback others unless there is a very strong relationship between the queries

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[cbearden]

i'll try it