+ Reply to Thread
Results 1 to 4 of 4

Using Worksheets Function in Formulas

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Using Worksheets Function in Formulas

    Good evening,

    i am attempting to create a VBA macro that will add various formulas to columns and am wondering if it is possible to use the worksheets function in the formula instead of a specific sheet name. My thinking is, I would want the macro to run even if the end user were to change the name of the worksheet. Below is my formula. Is it possible to replace Sheet1 with worksheets(2)?

    =INDEX(Sheet1!$S$3:$S$6887,MATCH(E61,Sheet1!$B$3:$B$6887,0))

    Thanks.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Using Worksheets Function in Formulas

    Sheet1 is the sheet code name, not the Sheet name. Changing the sheet name will have no effect.

    however,
    Please Login or Register  to view this content.
    Last edited by Tinbendr; 01-08-2015 at 12:05 PM.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Using Worksheets Function in Formulas

    Tinbendr,

    Your response is much appreciated. I did not even think about using the CodeName/Name properties.

    I ended up using the Worksheets(2).Name function which worked perfectly. Finish product below. Are there any benefits of using the CodeName instead of the Name property?

    "=INDEX(" & Worksheets(2).Name & "!$S$3:$S$" & ASSRLastRow & ",MATCH($E2," & Worksheets(2).Name & "!$B$3:$B$" & ASSRLastRow & ",0))"

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Using Worksheets Function in Formulas

    Appartently, I was wrong. You can't use the CodeName in the builtin function. The 'name!' always refers to the Sheet name. But Excel is supposed to update the formulas if the sheet name is changed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to replace new name of worksheets in formulas in newly copied worksheets
    By rikk1965 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-31-2013, 11:05 AM
  2. Formulas between Worksheets
    By crafty carper in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-22-2011, 05:03 AM
  3. Excel 2007 If function using 2 worksheets returning formulas
    By kiepre in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-01-2009, 08:48 AM
  4. Formulas across worksheets
    By Sionos in forum Excel General
    Replies: 0
    Last Post: 08-08-2007, 06:18 AM
  5. Formulas in Worksheets
    By MusicMan in forum Excel General
    Replies: 1
    Last Post: 11-15-2005, 06:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1