+ Reply to Thread
Results 1 to 4 of 4

Variable to store name of sheet

  1. #1
    Registered User
    Join Date
    09-10-2016
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2013
    Posts
    11

    Variable to store name of sheet

    So I have about 60 rows of data on a sheet titled November that I use to populate a pivot table. Long story short, the way I have it set up is that every month, I create a new 60 rows that reference values on a new sheet representing a new month. The problem that I've run into is figuring out a way to automate the way I reference the new sheet representing the new month. For example, I have a function: =SUMIF(November!N:N, "Gas",November!K:K). What I was thinking was setting it up to where the "November" part of the function is a variable that is defined in one cell. So instead of going through each of the 60 rows and changing the "November" part to it into whatever the name of the new sheet is, I change the value of one cell, which all 60 rows reference, which would allow me to save a ton of time in the future. Is there a way of doing that?
    Attached Files Attached Files
    Last edited by dv1651st; 12-01-2021 at 04:48 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,481

    Re: Variable to store name of sheet

    Hi there,

    See if the attached version of your workbook does what you need.

    Cell C2 contains the following formula which has been copied downwards as far as cell C10:

    Please Login or Register  to view this content.
    Cell E2 has been declared as the Named Range "ptrMonth" which contains data validation to allow the selection of any month name.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-10-2016
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Variable to store name of sheet

    Perfect! I did not know about the ampersand symbol. Thank you for teaching me!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,481

    Re: Variable to store name of sheet

    Hi again,

    Many thanks for your feedback.

    You're welcome - glad I was able to help.

    Regards,

    Greg M

+ 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. Function for various variable types, how to store the variable?
    By Scotster in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2018, 12:18 PM
  2. How to store a value of a variable so that it can be used later ?
    By yoursamrit2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2014, 03:29 PM
  3. [SOLVED] Store RGB(x, y, z) As Variable
    By ntitled in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2014, 01:05 PM
  4. Variable to store last row more than once.
    By bopsgtir in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2012, 04:56 PM
  5. [SOLVED] Store variable in worksheet and use that variable in VBA code
    By ExcelGal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2012, 08:20 AM
  6. Store name as variable
    By james.90 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2008, 12:22 AM
  7. store variable
    By keen learner in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-08-2006, 10:30 AM

Tags for this Thread

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