Data reference retrieval

  • Thread starter Thread starter tonym
  • Start date Start date
T

tonym

I need help with an excel sheet! here is my data sheet.

product qty price total
Spark plugs
classic 2 5 10
fox 8 4 32
sn95 2 8 16
mod 5 4 9
Total 67


Floor mats
all 10 15 150
total 150


Headlights
classic 2 7 14
all 1 10 10
total 24


I need a summary sheet that will go to the data sheet, extrac
inventory totals, by type and report them to a summary. For example
based on the above data, i need it to show:

Spark plugs 67
Floor mats 150
lights 24


Please note: there may be more than one entry in each category, an
they can change from day to day. I tried functions, and it wouldn
work. I need the formula to go to the data sheet, find Spark plugs
then input the $ amount of total on the summary sheet.

Anyone know how this is done??
Thank you in advance for any help offere
 
If you change the layout of your data, you can create a summary by using
a pivot table or use an AutoFilter to view specific items. Add a column
for the category, e.g.:

Category Product Qty Price Total
Spark plugs classic 2 5 10
Spark plugs fox 8 4 32
Spark plugs sn95 2 8 16
Spark plugs mod 5 4 20
Floor mats all 10 15 150
Headlights classic 2 7 14
Headlights all 1 10 10

There are instructions in Excel's Help, and here:

AutoFilter
http://www.contextures.com/xlautofilter01.html
PivotTables
http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
I dont understand.... Can someone write the formula for one so I ca
maybe understand it. I used the Function wizard and it always gives m
N/A. here is the actual data

DEST TOTAL
ACC002 50,725.00
CHCC001
CHKCC001
CNCC001 81,100.00
CNCC003
CNCC006 145,025.00
CNCC007 76,500.00
CNCC008 103,850.00
CNCC009 55,525.00
CNCC010 76,500.00
CNCC011 141,025.00
CNCC012
CNCC015
CTL001
CYCC001
FAIR004
FAIR005
FCB001 20,650.00
FCB002
FCB003
FCB004 60,525.00
FCB005 151,225.00
FCB006
FCB007
FCB014 60,750.00
FCB015 20,500.00
PATN001
PEB1618
PEB1624
PEB1626
PEB1631
PEB1639
PEB7001 173,250.00
PEB7003
PEB7006
PEB7007
PEB7008 143,100.00
PEB7009
PEB7010 80,775.00
PEB7011 117,025.00
PEB7012
PEB7013 271,650.00
PEB7016
PEB7017 211,300.00
PEB7018
PEB7019 90,875.00
PEB7021 120,250.00
PEB7022 120,500.00
PEB7023
PEB7027 131,500.00
PEB7029
PEB7029A
PEB7032 120,250.00
PEB7041 116,000.00
PEB7044 97,875.00
PEB7045
PEB7048 79,000.00
PEB7052 80,500.00
PEB7061 62,100.00
PEB7062 85,350.00
PEB7063
PEB7069
PEB7072
PEB7074 53,400.00
PEB7080
PEB7081
PEB7090
PEB7092 13,600.00
PEB7094
PEB7096
PEB7097 91,750.00
PEB7098
PEB7101
PEB7103 61,025.00
PEB7108 118,000.00
PEB7109
PEB7111 161,525.00
PEB7113 124,000.00
PEB7115
PEB7116 260,000.00
PEB7120
PEB7122
PEB7124 80,550.00
PEB7132
PEB7134
PEB7138 191,500.00
PEB7146 201,000.00
PEB7148 60,500.00
PEB7160
PEB7162
PEB7166
PEB7167
PEB7169 102,500.00
PEB7170
PEB7171
PEB7174 292,500.00
PEB7178
PEB7207
PEB7221 5,500.00
PEB7222
PEB7226
PEB7228
PEB7230
TSB001


the 3 digit code is the bank and the number is the branch #. The
amount is the total for each branch. Some branches may not have a
amount for a day.

Here is what i need. I would need a summary worksheet. On the summar
worksheet, for the CNC total, I need it to go, add up all the CNC code
branches, and give me the total.

so the summary would look like

cnc $679,525
 
Hi

Try
=SUMPRODUCT(--(Left($A$2:$A$1000,3)="CNC")*$B$2:$B$1000)
Change ranges to suit your data.

If you listed the 3 letter codes you required in sya cells E1:E20 you could
put into cell F1
=SUMPRODUCT(--(Left($A$2:$A$1000,3)=E1)*$B$2:$B$1000)
then copy the formula down to F20 and you would have the totals for each
code
(Again, change ranges to suit the total number of codes required)
 
I need a summary sheet that will go to the data sheet, extract
inventory totals, by type and report them to a summary. For example,
based on the above data, i need it to show:

Spark plugs 67
Floor mats 150
lights 24
Click to expand...

It may help to rearrange your data:

Spark plugs classic 2 5 10
Spark plugs fox 8 4 32
Spark plugs sn95 2 8 16
Spark plugs mod 5 4 9
Floor mats all 10 15 150
headlights classic 2 7 14
headlights all 1 10 10

If you do so, you can use Data/Group and Outline, Data/Subtotals, and
Data/Consolidate commands.

If you can't do this, there's an alternate solution -- let me know if you
want it.
 
Back
Top