Excel file linking

[spinal2k2]

Hi,

At work we are currently making a new spread sheet set up to manage
figures etc (well we are testing it etc).

There are a number of different spread sheet files that are all the
same apart from each one having some rows at the top with different
info, the totals from them are all linked into a separate totals
sheet.

But the main files spread sheets all have the same info area in them
(ie same info in the cells size formatting etc) and have the info in
some cells protected (ie so they cant be accidently wiped out) (these
all have a grey cell fill) white cells are where the info is entered).

I know its possible to link cell info from one file to another but is
it possible to link the cell formatting from one file to another.

Ie so we have a master template with the info area which the other
files get the cell info, formulas and formatting from). (so that if we
needed to add, delete cells/info etc we would do it via the master and
the rest update from it.

The next one question is, is it possible to when an excel file is
opened excel pops up a message asking for a name or (if possible an
ID# which when entered it matches to a name and puts the name into a
certain cell).

Ie open the file, excel asks for User # - enter 12345 and 12345 is
linked/matched to Joe Bloggs and it auto enters it into the set cell.

 

[Matthew Pfluger]

No, it is not possible (without macros) to link formatting. Also, I think
you are asking about creating a template and whenever this template is
updated all files that use this template will also be updated? No, this is
also not possible (or more appropriately, feasible. It would be very
difficult to program and manage.)

However, it IS possible and simple to prompt for a name upon opening a file.
Put the macro below into a standard code module, and it will run
automatically whenever the file is opened.

Sub Auto_open()
Dim response As String
Dim UserName As String
response = InputBox("Please enter a User #.")
' perform error checking here

Select Case response
Case 12345
UserName = "Joe Bloggs"
Range("A1").Value = UserName

Case Else
' you should have some default value here
End Select
End Sub

HTH,
Matthew Pfluger