I have created a template that has several identical worksheets in a workbook
with an overall summary sheet that provides a report of the totals from all
worksheets. This is used as an expense system to assign costs to various
clients and provide an overall summary for VAT and employee reimbursement
purposes. Because the client list changes from year to year I have named the
worksheets Client 1, Client 2 etc.

What I want to do is to allow the person administering this sytem to rename
a worksheet and have that name automatically update the "Client name" cell at
the top of the worksheet, and thus be linked through to the summary sheet.
The problem is that I want each worksheet to have its own name - I used the
Microsoft KB to find out how to do this for the active worksheet and this
works fine but I cannot work out how to get this done for each worksheet
individually whether they are active or not.

Just had a thought - it really doesn't matter where the Client name is
entered so long as it reflects through to the worksheet name shown on the tab
and to the summary sheet. If it were easier to program the Client name could
be entered in the worksheet cell so long as the worksheet name is updated
automtically to reflect the change.

Alternatively, because each row in the summary sheet is made up of data
linked from the relevant worksheet the name of the worksheet is in the link.
If it could be extracted and put into a cell on the summary sheet I could
link this to the cell on the relevant worksheet.

Just a couple of additional thoughts - I have not got either to work myself.


"robertmharrison" wrote:

I have created a template that has several identical worksheets in a workbook
with an overall summary sheet that provides a report of the totals from all
worksheets. This is used as an expense system to assign costs to various
clients and provide an overall summary for VAT and employee reimbursement
purposes. Because the client list changes from year to year I have named the
worksheets Client 1, Client 2 etc.

What I want to do is to allow the person administering this sytem to rename
a worksheet and have that name automatically update the "Client name" cell at
the top of the worksheet, and thus be linked through to the summary sheet.
The problem is that I want each worksheet to have its own name - I used the
Microsoft KB to find out how to do this for the active worksheet and this
works fine but I cannot work out how to get this done for each worksheet
individually whether they are active or not.

Robert,

Not really sure I get the whole question, but you can get the sheet name in
a cell with the formula

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"robertmharrison" wrote in
message ...
Just had a thought - it really doesn't matter where the Client name is
entered so long as it reflects through to the worksheet name shown on the
tab
and to the summary sheet. If it were easier to program the Client name
could
be entered in the worksheet cell so long as the worksheet name is updated
automtically to reflect the change.

Alternatively, because each row in the summary sheet is made up of data
linked from the relevant worksheet the name of the worksheet is in the
link.
If it could be extracted and put into a cell on the summary sheet I could
link this to the cell on the relevant worksheet.

Just a couple of additional thoughts - I have not got either to work
myself.


"robertmharrison" wrote:

I have created a template that has several identical worksheets in a
workbook
with an overall summary sheet that provides a report of the totals from
all
worksheets. This is used as an expense system to assign costs to various
clients and provide an overall summary for VAT and employee
reimbursement
purposes. Because the client list changes from year to year I have named
the
worksheets Client 1, Client 2 etc.

What I want to do is to allow the person administering this sytem to
rename
a worksheet and have that name automatically update the "Client name"
cell at
the top of the worksheet, and thus be linked through to the summary
sheet.
The problem is that I want each worksheet to have its own name - I used
the
Microsoft KB to find out how to do this for the active worksheet and
this
works fine but I cannot work out how to get this done for each worksheet
individually whether they are active or not.

Hi Robert, here is my way to do that:

1) Create yout own function, e.g.:

Function MONTHNUMBER(rAnyCell)
Application.Volatile
MONTHNUMBER = rAnyCell.Parent.Name
End Function

2) use this function as formula in cell, e.g.:´
=MONTHNUMBER(A1)

Vlado