Moving Table Data to other worksheets.

[Guest]

I am working with an order form wherein I have a table of about 100 items
setup like this:

A B C D
1 Qt Desc Unit Price Extended Price
2 A $300
2 B $450
4 C $500

Column D is a calculated by multiplying Column A times Column C

A user will input the Qt they want to order in Column A and the worksheet
will obviously calculate the extended price for those items in Column D.

What I want to do is to move the data to another worksheet that will give a
consolidated list of items ordered.

It would look like this:
A B C D
1 Qt Desc Unit Price Extended Price
2 2 J $200 $400
3 5 AE $150 $750
4 4 BD $300 $1200
5 Total $2350


I know how to reference individual cells from one worksheet to another but
with 100 different items, I do not want to have 100 rows on the order report
worksheet (unless of course a customer is ordering at least 1 of each of the
100 items available)

How to I consolidate this data? Is there a function that will look at each
row and compare and return a value to the other worksheet only if the value
is true?

Any help would be greatly appreciated.

 

[Biff]

Hi!

===============

I know how to reference individual cells from one worksheet to another but
with 100 different items, I do not want to have 100 rows on the order report
worksheet (unless of course a customer is ordering at least 1 of each of the
100 items available)
===============

If you want to use formulas to do this then you'll have to have 100 rows
with formulas to cover the possibility that an order may indeed be all 100
items. Probably very unlikely but you have to be prepared!

That doesn't mean your consolidated list will be 100 rows long even if an
order is placed for only 5 items.

Take a look at this sample file:

http://s64.yousendit.com/d.aspx?id=3NBSRNL23OZ563I08ABLFOOE5I

Enter some quantities on Sheet1 in column A then look at Sheet2.

Biff

 

[Biff]

Ooops!

Disregard that first link! When I setup the file I used named ranges then
deleted them. I forgot to change THE KEY formula to reflect that!!!!

Try this link:

http://s64.yousendit.com/d.aspx?id=2HEP38DPGLMM02KQZ1T4BWI9MO

Biff

 

[Guest]

Thanks,
I think that this is exactly the solution that I was looking for. Now I will
study the formulas to understand what the program is doing and will replicate
this type of formula to my own application.

 

[Guest]

Biff,
I tried to duplicate your formulas and keep coming up with Value errors. Is
there something I am missing?

Your help is appreciated

Chuck