Referencing cells to/from workbooks`

Vlad

New Member
Joined
Aug 31, 2002
Messages
7
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Aug 31, 2002
Messages
7
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Spammer
Joined
Mar 14, 2002
Messages
15
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

New Member
Joined
Aug 31, 2002
Messages
7
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
 

Forum statistics

Threads
1,144,273
Messages
5,723,437
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top