Lookups and variable sums

brentm · 01-07-2005, 06:06 PM

Hey all. This will probably be an easy one for the experts, since I am
probably thinking too much. Here is my dilemma: I have a query through
office connector that pulls information from one software package into excel.
Each time this query is run, the size of the data may vary (ie there may be
8000 rows one time and 12000 the next). So here is the specifics: column a
lists the time entries for each employee during a specific time period, like
monthly or quarterly. I want to sum the values in column d for each employee.
Example:
Column A Column B Column C Column D
Column E
JOHNDOE01 20 Hours 15/Hour 300.00
SUMIF Formula
JOHNDOE01 35 Hours 15/Hour 525.00
SUMIF Formula
JOHNDOE01 25 Hours 15/Hour 375.00
SUMIF Formula
JOHNDOE01 25 Hours 15/Hour 375.00
SUMIF Formula
JANEDOE01 25 Hours 18/Hour 450.00
SUMIF Formula
JANEDOE01 30 Hours 18/Hour 540.00
SUMIF Formula
JANEDOE01 10 Hours 18/Hour 180.00
SUMIF Formula

=SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
Sheet3 has the master list of employees

I put the formula in E1, then drug the formula down. As I did this, the
formula changed to:
=SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
Is there a way I can code Column E without having to cut and paste to adjust
the formula for each row?

Thanks.

Jim Thomlinson · 01-07-2005, 07:06 PM

This might be a silly question but why not just do a pivot table. Your data
is perfect for this and it will get you around the whole formula problem.

Try this... Choose Data->Pivot Table and just follow the instructions
Place the names in the right hand column and the amounts in the middle...

"brentm" wrote:

> Hey all. This will probably be an easy one for the experts, since I am
> probably thinking too much. Here is my dilemma: I have a query through
> office connector that pulls information from one software package into excel.
> Each time this query is run, the size of the data may vary (ie there may be
> 8000 rows one time and 12000 the next). So here is the specifics: column a
> lists the time entries for each employee during a specific time period, like
> monthly or quarterly. I want to sum the values in column d for each employee.
> Example:
> Column A Column B Column C Column D
> Column E
> JOHNDOE01 20 Hours 15/Hour 300.00
> SUMIF Formula
> JOHNDOE01 35 Hours 15/Hour 525.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JANEDOE01 25 Hours 18/Hour 450.00
> SUMIF Formula
> JANEDOE01 30 Hours 18/Hour 540.00
> SUMIF Formula
> JANEDOE01 10 Hours 18/Hour 180.00
> SUMIF Formula
>
> =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
> Sheet3 has the master list of employees
>
> I put the formula in E1, then drug the formula down. As I did this, the
> formula changed to:
> =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
> Is there a way I can code Column E without having to cut and paste to adjust
> the formula for each row?
>
> Thanks.

Jim Thomlinson · 01-07-2005, 07:06 PM

If you want your formulas to work look up these two items

Absolulte References or better yet named ranges...

HTH

"brentm" wrote:

> Hey all. This will probably be an easy one for the experts, since I am
> probably thinking too much. Here is my dilemma: I have a query through
> office connector that pulls information from one software package into excel.
> Each time this query is run, the size of the data may vary (ie there may be
> 8000 rows one time and 12000 the next). So here is the specifics: column a
> lists the time entries for each employee during a specific time period, like
> monthly or quarterly. I want to sum the values in column d for each employee.
> Example:
> Column A Column B Column C Column D
> Column E
> JOHNDOE01 20 Hours 15/Hour 300.00
> SUMIF Formula
> JOHNDOE01 35 Hours 15/Hour 525.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JANEDOE01 25 Hours 18/Hour 450.00
> SUMIF Formula
> JANEDOE01 30 Hours 18/Hour 540.00
> SUMIF Formula
> JANEDOE01 10 Hours 18/Hour 180.00
> SUMIF Formula
>
> =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
> Sheet3 has the master list of employees
>
> I put the formula in E1, then drug the formula down. As I did this, the
> formula changed to:
> =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
> Is there a way I can code Column E without having to cut and paste to adjust
> the formula for each row?
>
> Thanks.

brentm · 01-07-2005, 07:06 PM

Jim,

Thanks for your input. I just started doing my own programming in Excel
last week, so I am still very green. I think the pivot table will be the
right solution. Thanks for the guidence.

Brent Mercer

"Jim Thomlinson" wrote:

> This might be a silly question but why not just do a pivot table. Your data
> is perfect for this and it will get you around the whole formula problem.
>
> Try this... Choose Data->Pivot Table and just follow the instructions
> Place the names in the right hand column and the amounts in the middle...
>
> "brentm" wrote:
>
> > Hey all. This will probably be an easy one for the experts, since I am
> > probably thinking too much. Here is my dilemma: I have a query through
> > office connector that pulls information from one software package into excel.
> > Each time this query is run, the size of the data may vary (ie there may be
> > 8000 rows one time and 12000 the next). So here is the specifics: column a
> > lists the time entries for each employee during a specific time period, like
> > monthly or quarterly. I want to sum the values in column d for each employee.
> > Example:
> > Column A Column B Column C Column D
> > Column E
> > JOHNDOE01 20 Hours 15/Hour 300.00
> > SUMIF Formula
> > JOHNDOE01 35 Hours 15/Hour 525.00
> > SUMIF Formula
> > JOHNDOE01 25 Hours 15/Hour 375.00
> > SUMIF Formula
> > JOHNDOE01 25 Hours 15/Hour 375.00
> > SUMIF Formula
> > JANEDOE01 25 Hours 18/Hour 450.00
> > SUMIF Formula
> > JANEDOE01 30 Hours 18/Hour 540.00
> > SUMIF Formula
> > JANEDOE01 10 Hours 18/Hour 180.00
> > SUMIF Formula
> >
> > =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
> > Sheet3 has the master list of employees
> >
> > I put the formula in E1, then drug the formula down. As I did this, the
> > formula changed to:
> > =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
> > Is there a way I can code Column E without having to cut and paste to adjust
> > the formula for each row?
> >
> > Thanks.

Jim Thomlinson · 01-07-2005, 08:06 PM

If you want your formulas to work look up these two items

Absolulte References or better yet named ranges...

HTH

"brentm" wrote:

> Hey all. This will probably be an easy one for the experts, since I am
> probably thinking too much. Here is my dilemma: I have a query through
> office connector that pulls information from one software package into excel.
> Each time this query is run, the size of the data may vary (ie there may be
> 8000 rows one time and 12000 the next). So here is the specifics: column a
> lists the time entries for each employee during a specific time period, like
> monthly or quarterly. I want to sum the values in column d for each employee.
> Example:
> Column A Column B Column C Column D
> Column E
> JOHNDOE01 20 Hours 15/Hour 300.00
> SUMIF Formula
> JOHNDOE01 35 Hours 15/Hour 525.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JOHNDOE01 25 Hours 15/Hour 375.00
> SUMIF Formula
> JANEDOE01 25 Hours 18/Hour 450.00
> SUMIF Formula
> JANEDOE01 30 Hours 18/Hour 540.00
> SUMIF Formula
> JANEDOE01 10 Hours 18/Hour 180.00
> SUMIF Formula
>
> =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
> Sheet3 has the master list of employees
>
> I put the formula in E1, then drug the formula down. As I did this, the
> formula changed to:
> =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
> Is there a way I can code Column E without having to cut and paste to adjust
> the formula for each row?
>
> Thanks.

brentm · 01-07-2005, 08:06 PM

Jim,

Thanks for your input. I just started doing my own programming in Excel
last week, so I am still very green. I think the pivot table will be the
right solution. Thanks for the guidence.

Brent Mercer

"Jim Thomlinson" wrote:

> This might be a silly question but why not just do a pivot table. Your data
> is perfect for this and it will get you around the whole formula problem.
>
> Try this... Choose Data->Pivot Table and just follow the instructions
> Place the names in the right hand column and the amounts in the middle...
>
> "brentm" wrote:
>
> > Hey all. This will probably be an easy one for the experts, since I am
> > probably thinking too much. Here is my dilemma: I have a query through
> > office connector that pulls information from one software package into excel.
> > Each time this query is run, the size of the data may vary (ie there may be
> > 8000 rows one time and 12000 the next). So here is the specifics: column a
> > lists the time entries for each employee during a specific time period, like
> > monthly or quarterly. I want to sum the values in column d for each employee.
> > Example:
> > Column A Column B Column C Column D
> > Column E
> > JOHNDOE01 20 Hours 15/Hour 300.00
> > SUMIF Formula
> > JOHNDOE01 35 Hours 15/Hour 525.00
> > SUMIF Formula
> > JOHNDOE01 25 Hours 15/Hour 375.00
> > SUMIF Formula
> > JOHNDOE01 25 Hours 15/Hour 375.00
> > SUMIF Formula
> > JANEDOE01 25 Hours 18/Hour 450.00
> > SUMIF Formula
> > JANEDOE01 30 Hours 18/Hour 540.00
> > SUMIF Formula
> > JANEDOE01 10 Hours 18/Hour 180.00
> > SUMIF Formula
> >
> > =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155),D2:D15485)
> > Sheet3 has the master list of employees
> >
> > I put the formula in E1, then drug the formula down. As I did this, the
> > formula changed to:
> > =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156),G3:G15486) and so on.
> > Is there a way I can code Column E without having to cut and paste to adjust
> > the formula for each row?
> >
> > Thanks.