+ Reply to Thread
Results 1 to 3 of 3

How to define a variable name or constant that can be used as part of a formula name?

  1. #1
    Registered User
    Join Date
    07-03-2009
    Location
    Rochester, MN
    MS-Off Ver
    Excel 2002
    Posts
    3

    How to define a variable name or constant that can be used as part of a formula name?

    Is there a way to define a name that can be used as a reference or variable? I have a sheet with my print forms on them. I also have account sheets for each month. I want the print form to take values from a defined month sheet, fill in the fields and then print it. Is there a way top define a constant or variable that can be used in a formula name? This is what I am trying to do.

    Cell C9 on the print form imports data from =Jan!C4. I have 50 lines in column C and other columns on the Jan sheet that needs to be imported into the print form. How can I defined a constant or name such that I can use the syntax =PrintMonth!C4, where PrintMonth can be changed to point to the month I want to print. PrintMonth would be used in every column/row as a reference to the correct cell on the month sheets.

    I tried to define a name PrintMonth that could be used as a variable but Excel turns that into ='Jan' which cannot be used as a variable in the sheet path to cell Jan!C4.

    What am I missing or doesn't Excel have a concept of a variable in a formula path? I have hundreds of cells that I need to reference from the print form sheet. It would be insane to have to create a name for each cell. Or is this a job for a macro or VBA?

    Thanks,
    Barry

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,444

    Re: How to define a variable name or constant that can be used as part of a formula n

    Hi,

    Try using the indirect function with your named range. Ie,

    =INDIRECT(PrintMonth&"!"&"C4")
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    07-03-2009
    Location
    Rochester, MN
    MS-Off Ver
    Excel 2002
    Posts
    3

    Re: How to define a variable name or constant that can be used as part of a formula n

    Thanks Dave, that did it.
    Barry

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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