Automatically moving data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

multiplan - 30 Oct 2004 23:46 GMT

I?d like to retrieve data from a table in sheet 1 into financia
statements in sheet 2, sheet 3, sheet 4 and sheet 5.

Within data source sheet 1:
Column A has various cities ? New York, LA, Boston etc
Column B has various fast food chains ? McDonalds, Burger King, Domino
Pizza etc
Column C has various food selections ? fries, burgers, pizza, drink
etc
Column D has food quantity sold

Sheet 2 is actually titled Fries sold
Sheet 3 is actually titles Burgers sold
Sheet 4 is actually titled pizzas sold
Sheet 5 is actually titled drinks sold

Okay, within each sheet 2 to 5, will list the city first in cell a1 an
then the fast food chains form cell a2 to cell a5. Then cell a7 wil
show the next city then the food chains within that city from cell a
to cell a11 and so on. Column b will need to be populated wit
quantity units sold against each fast food chain within the city fro
source data in sheet1

How do I get quantity sold data from data from in sheet1 copy int
sheets 2 to 5? I don't want to manually key in the data because th
source data has approximately 2000 rows of data to be copies to eithe
sheet 2 to sheet

Reply to this Message

Max - 31 Oct 2004 03:27 GMT

One way to suit your layout ..

In your sheet: Fries Sold
-----------------------

Put in B2:
=SUMPRODUCT((Sheet1!$A$1:$A$10=$A$1)*(Sheet1!$B$1:$B$10=A2)*(Sheet1!$C$1:$C$
10="Fries"),Sheet1!$D$1:$D$10)
Copy down to B5

Put in B8:
=SUMPRODUCT((Sheet1!$A$1:$A$10=$A$7)*(Sheet1!$B$1:$B$10=A8)*(Sheet1!$C$1:$C$
10="Fries"),Sheet1!$D$1:$D$10)
Copy down to B11

Put in B14:
=SUMPRODUCT((Sheet1!$A$1:$A$10=$A$13)*(Sheet1!$B$1:$B$10=A14)*(Sheet1!$C$1:$
C$10="Fries"),Sheet1!$D$1:$D$10)
Copy down to B17

And so on ..

Change the ranges to suit (above assumes only rows 1 to 10 in Sheet1)

--
Repeat the above constructs in the other sheets,
just change the hardcoded "Fries" to "Burgers", "Pizza", etc

Signature

Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

>
> I?d like to retrieve data from a table in sheet 1 into financial
[quoted text clipped - 24 lines]
> source data has approximately 2000 rows of data to be copies to either
> sheet 2 to sheet 5

Reply to this Message