How do I dynamically update the name of a worksheet in Excel?

Question

Hi,

I have an Excel spreadsheet which is being used to specify filesystem build information for our Unix team.

This worksheet would have different information depending on the system to be built. For example, a production system would have many filesystems, whereas a development system only one or two.

So based on user input, we would populate one or other of the two input worksheet formats ("dev","prod") but the destination worksheet will be always be the same. Only one of the dev or prod worksheets would be visible and active for users to input data at any given time.

How do we tell Excel to use a particular input worksheet to calculate another output worksheet?

My initial idea was that, for simplicity's sake, the "output" worksheet would take its data from an "input" worksheet, and that we would dynamically make either the "dev" or "prod" template become the "input" worksheet. Is this the right way to do this, or is there some better method?

Thanks in advance,

Mark

Answer 1

There's a few ways depending on the complexity of the sheet:

• If you only have a few worksheets, and users would just select "Production" or "Test", you could set up the conditional logic in the formulas. For example: =if(A1="Test",TestSheet!A1 + TestSheet!A2, ProdSheet!A1 + ProdSheet!A2)
• You could utilize the same type of idea as above, but add more complexity using vlookup tables
• Your VBA code could create the entire Excel spreadsheet dynamically based on the user input

Answer 2

Hi, if you can use VBA code as LuckyLindy suggests, you can do pretty much anything. What I used to do is record macros for various operations, for example adding or renaming sheets and take the generated code and adapt it to do exactly what was required.

An example of simple macro code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "My Sheet"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "My Sheet 2"
End Sub

You can record very complex operations and manipulate the generated code and place it behind specific events. So if you wanted the code to fire on worksheet load or activation of a sheet, you can insert it in the relevant block: i.e.

'in Sheet1 - code behind (alt+F11 shortcut)
Private Sub Worksheet_Activate()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "My Sheet"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "My Sheet 2"
End Sub

HTH

Answer 3

Another way would be to use the INDIRECT formula.

Say you have in cell A1 the name of the sheet you want to look up input from, then the following will look up the value of cell B7 from that sheet:

=INDIRECT($A$1,"!B7")