Excel Worksheet Referencing

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Have two problems: -


    1) Is there any way of referencing cells on another worksheet other than using the worksheet name!cellname? My worksheet names are v. long, which makes formulas cumbersome. Is there a shorter way of referencing?


    2) Is there a way of protecting the sheet name so it cannot be changed? The only way I can see is protecting the whole workbook. However, I also want to be able to add new worksheets, which protecting the workbook prevents.


    Any help greatly appreciated.

    Hi,


    1) Yes, Iagree that it can be complex formulas. One way is to use cellnames like "MyName" that refer to =[Book1]Sheet1!$F$18


    [*]Select one cell in the sourc worksheet
    [*]Choose the command Insert | Name
    [*]Define...
    [*]Create the name
    [*]Switch over to the target worksheet
    [*]Select the targetcell and
    [*]Enter =MyName
    [/list]


    2) Hm, I don´t think so. What You can do is to either give the users some utility that allow them to add sheets etc or if it's one or two sheets You want to name-protect then You can check the name and correct them via:


    Private Sub Worksheet_Activate()


    End Sub


    Private Sub Worksheet_Deactivate()


    End Sub


    Kind regards
    Dennis

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login