View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
M Kan M Kan is offline
external usenet poster
 
Posts: 169
Default How do I create a table on a new worksheet from other worksheet da

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.