Referencing cells to/from workbooks`

[Vlad]

Hi ya'll,

If I need to specify the source cell from book1 and cell A1--it's simple. But what if I need to preserve all the custom settings, formats, colors, borders, etc.?

If it's simply one-off job, it's easy: copy, paste special, all except borders.

Is there anything similar for linking/referencing?

many thanks

 

[PaddyD]

Vlad,

A formula is going to return a value, not info about the worksheet environment (which includes things like cell formats etc). To get a cell's value & the format info, you'll need to use VBA - see

http://mrexcel.com/board/viewtopic.php?topic=20821&forum=2&2

for the type of code lines you'll need.

Post back with more details about what you're trying to do so we can take it further...

Paddy

 

[Vlad]

Well, in actual fact, here's the layout:

6 users need to feed information into one master file. The users can't see each other notes, so 7 files would be a good idea, don't you agree. (Well, unless we want to setup a password procedure per user, which I don't know how to do)

so, users (say User1 through User6) feed info into Master. The info needs to be updated regularly.

 

[PaddyD]

That may or may not be the best way to go about things. Either way, we'll need a lot more info about what your users are entering into the sheets to make progress...Will you need to bring in all the data, onl some each time...

Long term, though I think you'll probably be better off with an alternative solution.

How about this (just for the hell of it):

1) Name each sheet, one for each user + a master
2) Have a box pop up when the file is opened
3) Get users to enter the name of their sheet / some 'password'. (with appropriate error checks)
4) Associate names / passwords with sheets to restrict eople to viewing their sheet.

Paddy
This message was edited by PaddyD on 2002-09-03 21:58

 

[Pioneer-1752]

I have a similar situation in which I have several users who each fill out their individual timesheet (all individual workbooks that no one else can see). Then we have a master timesheet that is populated automatically from the individual files. I do this by selecting and copying the range on the individual sheet and then on the master doing a paste special...paste link. It works well for us. Hope this helps.

 

[Vlad]

Hey, CPALMER

Good advice! Works perfectly! I created a form button, recorded a macro, and assigned it to this button. Exactly after that I started getting unformatted data: just values.

I checked the tools/options/calculations: the update remote links is checked.

Any ideas?

It's showing custom formats for a tick and then resets it to just values.

Thanks