I have not done this before and I am a little out of my element.

I need to take data from an existing Excel 2003 worksheet and create a table
in a new worksheet, that summarizes data in the existing sheet based on
certain conditions.

I need to know the following:

1. Can I just create a table without having to first link it to the
existing worksheet? If it has to be linked, can someone give me instructions
to do that or direct me to somewhere that I can get the instructions?

2. Once I have the table created, I need to create a formula that
calculates amounts based on certain criteria. For example, here are my
column headers and data:

NAME CATEGORY TITLE AMOUNT HOURS
Jones, Jan Summer Landscaper $50.00 5
Smith, Mary K. Summer Laborer $50.00 6.25
Smith, Mary K. Summer Laborer $75.00 8
Smith, Mary K. Winter Landscaper $50.00 4
Adams, Mark Summer Mechanic $80.00 7
Wilson, John Winter Mechanic $80.00 25.75
Wilson, John Summer Landscaper $90.00 4.5
Wilson, John Fall Laborer $70.00 11

I want to create a table that shows each Name separately for each Category,
and for each Title, and for each Amount, with another column that will pull
the total hours worked for each entry (once the formula is written into it),
and then a final column that will calculate the total earned (again once the
formula is written into it).

I need a formula to pull the Name, Category, Title and Amount information
based on the criteria above from the existing worksheet to the table in the
new worksheet.

I think I would then copy this formula, and then change it to have the hours
field be the control in the formula for the Total Hours column, and then, do
a simple, Amount*Total Hours formula for the last column. If this is not
correct, feel free to comment.

Can someone help me?

Thank you in advance.

To your first point, you can highlight your table (CTRL C) and then paste the
links into your new worksheet (Edit: Paste Special: Paste Links). This will
paste links that are unformatted. Then, simple paste special again, but this
time choose Formats.

the formula for your summary would look like this:

=sumproduct(--(range of names,"name"),--(range of
categories,"category"),--(range of titles,"title"),range of amounts)

Here's a more in depth discussion

http://www.kan.org/tips/excel_sumproduct_advanced1.php

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Etg-Para" wrote:

I have not done this before and I am a little out of my element.

I need to take data from an existing Excel 2003 worksheet and create a table
in a new worksheet, that summarizes data in the existing sheet based on
certain conditions.

I need to know the following:

1. Can I just create a table without having to first link it to the
existing worksheet? If it has to be linked, can someone give me instructions
to do that or direct me to somewhere that I can get the instructions?

2. Once I have the table created, I need to create a formula that
calculates amounts based on certain criteria. For example, here are my
column headers and data:

NAME CATEGORY TITLE AMOUNT HOURS
Jones, Jan Summer Landscaper $50.00 5
Smith, Mary K. Summer Laborer $50.00 6.25
Smith, Mary K. Summer Laborer $75.00 8
Smith, Mary K. Winter Landscaper $50.00 4
Adams, Mark Summer Mechanic $80.00 7
Wilson, John Winter Mechanic $80.00 25.75
Wilson, John Summer Landscaper $90.00 4.5
Wilson, John Fall Laborer $70.00 11

I want to create a table that shows each Name separately for each Category,
and for each Title, and for each Amount, with another column that will pull
the total hours worked for each entry (once the formula is written into it),
and then a final column that will calculate the total earned (again once the
formula is written into it).

I need a formula to pull the Name, Category, Title and Amount information
based on the criteria above from the existing worksheet to the table in the
new worksheet.

I think I would then copy this formula, and then change it to have the hours
field be the control in the formula for the Total Hours column, and then, do
a simple, Amount*Total Hours formula for the last column. If this is not
correct, feel free to comment.

Can someone help me?

Thank you in advance.

But is it possible to create a table from data in a worksheet? In other
words, I have the data as I typed below in my original post, but I do not
have any table. I want to create the table on a new worksheet, and then
after it has been created, I want to be able to create the formula within it
based on the criteria I listed in my original post.

I may just not be understanding your post to my original question, so I
apologize in advance if that is the case, but any further assistance would be
appreciated.

Thanks again.

Etg

"M Kan" wrote:

To your first point, you can highlight your table (CTRL C) and then paste the
links into your new worksheet (Edit: Paste Special: Paste Links). This will
paste links that are unformatted. Then, simple paste special again, but this
time choose Formats.

the formula for your summary would look like this:

=sumproduct(--(range of names,"name"),--(range of
categories,"category"),--(range of titles,"title"),range of amounts)

Here's a more in depth discussion

http://www.kan.org/tips/excel_sumproduct_advanced1.php

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Etg-Para" wrote:

I have not done this before and I am a little out of my element.

I need to take data from an existing Excel 2003 worksheet and create a table
in a new worksheet, that summarizes data in the existing sheet based on
certain conditions.

I need to know the following:

1. Can I just create a table without having to first link it to the
existing worksheet? If it has to be linked, can someone give me instructions
to do that or direct me to somewhere that I can get the instructions?

2. Once I have the table created, I need to create a formula that
calculates amounts based on certain criteria. For example, here are my
column headers and data:

NAME CATEGORY TITLE AMOUNT HOURS
Jones, Jan Summer Landscaper $50.00 5
Smith, Mary K. Summer Laborer $50.00 6.25
Smith, Mary K. Summer Laborer $75.00 8
Smith, Mary K. Winter Landscaper $50.00 4
Adams, Mark Summer Mechanic $80.00 7
Wilson, John Winter Mechanic $80.00 25.75
Wilson, John Summer Landscaper $90.00 4.5
Wilson, John Fall Laborer $70.00 11

I want to create a table that shows each Name separately for each Category,
and for each Title, and for each Amount, with another column that will pull
the total hours worked for each entry (once the formula is written into it),
and then a final column that will calculate the total earned (again once the
formula is written into it).

I need a formula to pull the Name, Category, Title and Amount information
based on the criteria above from the existing worksheet to the table in the
new worksheet.

I think I would then copy this formula, and then change it to have the hours
field be the control in the formula for the Total Hours column, and then, do
a simple, Amount*Total Hours formula for the last column. If this is not
correct, feel free to comment.

Can someone help me?

Thank you in advance.