Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use variables in formulas (excel 2000)

    I have a formula that i want to introduce variables into but am unsure how to do so.

    =SUM('employee name'!Q$113:Q$123)+SUM('employee name'!U$113:U$123)+SUM('employee name'!W$113:W$123)
    is the formula

    I would like to have the "employee name" as a variable, and have the row ranges (not column) as well. Ideally, the formula is copied identically down Column B along a list of all employees in Column A(each have their own worksheet), getting the sheet name to reference from Col A for each row. At the top is a spot to enter the start and end row ranges that I can change rapidly across all formulas.

    So (in my mind) the formula would look like this:
    =SUM('VAR1'!Q$VAR2:Q$VAR3)+SUM('VAR1'!UVAR2:U$VAR3 )+SUM('VAR1'!W$VAR2:W$VAR3)

    I hope that is clear. I'm sure I'm missing something simple...

    TIA

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use variables in formulas (excel 2000)

    Is this in VBA code, or is it on a worksheet? If it is on a worksheet, what are VAR1, VAR2, etc.?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use variables in formulas (excel 2000)

    This is in a worksheet - the Summary worksheet.

    VAR1,2,3 are what I trying to figure out how to use - they are just how "I" think it should work...

    VAR1 = the text in column A; the employee name/name of relevant (employee) worksheet (with raw datat)
    VAR2 = first row i want to summarize of the raw data
    VAR3 = last row i want to summarize of the raw data

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

    Re: use variables in formulas (excel 2000)

    Say that employee names are in A2, A3 etc., and that the first and last row to use are in B1 and C1, respectively.
    Enter the following formula in B2 (to the right of the first employee name):
    <code>
    =SUM(INDIRECT("'"&A2&"'!Q"&$B$1&":Q"&$C$1),INDIREC T("'"&A2&"'!U"&$B$1&":U"&$C$1),INDIRECT("'"&A2&"'! W"&$B$1&":W"&$C$1))
    </code>
    It may be displayed on more than one line in your browser, but it is really one long formula. You can fill it down as far as needed.

  5. #5
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use variables in formulas (excel 2000)

    I'll try that tomorrow but it looks like it is what I want.

    I wasn't doublequoting my singlequotes, if that makes sense [img]/forums/images/smilies/smile.gif[/img]

    Thanks!

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

    Re: use variables in formulas (excel 2000)

    The argument of INDIRECT is a string - it can be a fixed quoted string, or a reference to a cell containing text, or as in this example, a concatenated mixture of the two. The fixed parts are enclosed in double quotes.

Posting Permissions

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