Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a tab's name in a calculation (Excel 2002 SP3)

    Is there any way to use a tab's name in a calculation?

    Scenario: each week a new worksheet is added to a workbook (at the end of the workbook) and the name of the worksheet (the name of the tab) is changed to reflect the end date of the project (in the form dd MMM yyyy ... 13 Sep 2005). There are two columns that use the name of the previous workbook (i.e. 09 Sep 2005) in calculated fields (i.e. [='09 Sep 2005'!F2] and [=('09 Sep 2005'!E2/'09 Sep 2005'!E$59)*'09 Sep 2005'!G$63]).

    I'd like to be able to insert a field name, field type, or *something* in place of the tab name in order to make a template so that I don't have to manually change the tab name in the calculated cells in a new worksheet and then copy & paste those cells into the rest of the new worksheet.

    Reading previous threads it appears that I can determine the name of a tab, but I'd like to use that name in a calculation (as shown above). Note that the calculation will *always* refer to the previous sheet.

    Please do not hesitate to let me know what information I have not provided.

    Thanks in advance for your help!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a tab's name in a calculation (Excel 2002 SP3)

    Have a look at the INDIRECT worksheet function, it may do what you need.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a tab's name in a calculation (Excel 2002 SP3)

    You can easily do a Replace All to replace the worksheet name in all formulas at once.

    You could create a custom function that returns the name of the previous sheet, but it would force you to use INDIRECT in all formulas that refer to the previous sheet; that doesn't seem an attractive option.

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a tab's name in a calculation (Excel 2002 SP3)

    Hans,

    Thanks for the quick reply!

    Although not the prettiest solution, using Replace All is probably the easiest (especially for me!). I read through the Help contents for the Indirect Function but could not make sense out of how it would work for this application (without going a lot deeper than I want!).

    Thanks again for your input!

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a tab's name in a calculation (Excel 2002 SP3)

    Jan Karel,

    Thanks for the quick reply!

    I read through the Help contents for the Indirect Function but could not make sense out of how it would work for this application.

    I think that, for the moment, I'll perform a Replace All for the previous tab's name in each new tab.

    Thanks again for your input!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a tab's name in a calculation (Excel 2002 SP3)

    I realised later that I misread your question a bit, so INDIRECT isn't a solution indeed. Sorry.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •