Results 1 to 6 of 6

20060210, 23:52 #1
 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

20060210, 23:55 #2
 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

20060211, 00:02 #3
 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

20060211, 00:36 #4
 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.

20060211, 00:48 #5
 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!

20060211, 00:52 #6
 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.