need formula to pull info from another sheet

T

Toney

New Member
Joined
Nov 3, 2015
Messages
9
I need to pull prev yr sales data from a certain customer on a tab that has all customers data listed to another tab. I need to match customer # and item # to get correct specific info. Any suggestions?
Item #cust #Customer nameDescriptionPackagingCase QtyTotal Sales $
100382111111SLAY'S FIREWORKS202044.62
100748111111SLAY'S FIREWORKS403925
200095111111SLAY'S FIREWORKS10633.5
100382111112ADAY's FIREWORKS5237.56
100748111112ADAY's FIREWORKS201411.12

<tbody>
</tbody>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Hello Toney,

It's hard to tell exactly what you need from the description. Can you provide what is on the other sheet and what the expected results are. I think either you need to use SUMIFS using both the Item# and Cust # or you need a lookup function concatenating the item and cust number, but again I am not sure.
 
Upvote 0
here is the sheet I where I what to see the info from 1st sheet posted. see ???. I want to pull previous year's customer sales info for same item over as I am writing new order for customer.



Acct #722353TypeCCS Prod Total W#
CustNameSLAY'S FIREWORKS SA 245CS Promo # PALLETS
Date PLL8 Total Cost CS
RBU TermsWNX Total w/Prod Lia
BP Ship Act Ship BP
Order Notes Order #
Tot CS OrdL8Tot $ OrdTot CS COG EXT ORD GM% PY Tot CS PY Tot $
4 $455.28$273.1140.0%
Item #Item DescriptionCS QTY ORDCS CostTot EXT ORD $EXT CS COGItem GM%2017 CS PUR2017 $ PUR
101789ABLAZE TRAY 'C'278.48156.96101.5935.3%??????
100899ABSOLUTE POWER 'C'83.660.000.0039.7%#N/A
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody> </tbody>
 
Upvote 0
Hi Joyner,
I am pretty close to getting the formula. I need to pull previous year's info from 2nd sheet. I need drill down in the 2nd sheet to find how many cases were purchased by account #, then Item # so this info shows on sheet 1 under 2017 CS PUR.

Acct #722353TypeC CS Prod Total W#
CustNameSLAY'S FIREWORKS SA 245 CS Promo # PALLETS
Date PLL8 Total Cost CS
RBU TermsWNX Total w/Prod Lia
BP Ship Act Ship BP
Order Notes Order #
Tot CS OrdL8UOMTot $ OrdTot CS COG EXT ORD GM% PY Tot CS PY Tot $
8 #N/A$969.84#N/A
Item #Item DescriptionCS QTY ORDCS CostTot EXT ORD $EXT CS COGItem GM%2017 CS PUR2017 $ PUR
101789ABLAZE TRAY 'C'178.48CS78.4850.8035.3%
100899ABSOLUTE POWER 'C'83.66CS0.000.0039.7%
101111ABIG BANG 'C'4248.73CS994.91697.1529.9%
101206BIG BANG (CA VER)103.26CS0.000.0039.0%
100072BIG BOMB POLYBAG 'C'70.30CS0.000.0039.4%
102175BIG BOMB TRAY 'C'92.18CS0.000.0038.4%
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody> </tbody>
 
Upvote 0
Hi Joyner,
I need pull cases sold from previous year by matching account/ customer # and item # on sheet 2. Here is sheet 1 where I want the info to show from sheet 2.



Sheet 2 is listed first.
Item #New Itemcomp itemcust #Customer #Business UnitCategoryBPSeasonYearPLCustomer nameDescriptionPackagingCase QtyUOMTotal Sales $
100064141157314115731022068JULY2017L6T & T GIFTS L.L.C.BARGAIN BAG C NYP2/126.0CS288
100072220335522033551022041JULY2017L74 WAY STOP IIBIG BOMB POLYBAG C6/11.0CS74.01
1000727480517480511022060JULY2017L8ADDISON CHEVRON DBA FISH'S FIREWORKSBIG BOMB POLYBAG C6/15.0CS351.52
1000728211418211411022060JULY2017L8ALLEN'S FIREWORKS COMPANYBIG BOMB POLYBAG C6/16.0CS421.8
100072272224327222431022041JULY2017L5AMANDA FOSKUHLBIG BOMB POLYBAG C6/11.0CS82

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

sheet 1

Acct #722353TypeC CS Prod Total W#
CustNameSLAY'S FIREWORKS SA 245 CS Promo # PALLETS
Date PLL8 Total Cost CS
RBU TermsWNX Total w/Prod Lia
BP Ship Act Ship BP
Order Notes Order #
Tot CS OrdL8UOMTot $ OrdTot CS COG EXT ORD GM% PY Tot CS PY Tot $
8 #N/A$969.84#N/A
Item #Item DescriptionCS QTY ORDCS CostTot EXT ORD $EXT CS COGItem GM%2017 CS PUR2017 $ PUR
101789ABLAZE TRAY 'C'178.48CS78.4850.8035.3%
100899ABSOLUTE POWER 'C'83.66CS0.000.0039.7%
101111ABIG BANG 'C'4248.73CS994.91697.1529.9%
101206BIG BANG (CA VER)103.26CS0.000.0039.0%
100072BIG BOMB POLYBAG 'C'70.30CS0.000.0039.4%
102175BIG BOMB TRAY 'C'92.18CS0.000.0038.4%
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody> </tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Update Cells from Certain Tabs based on Value other cells
Replies
0
Views
591
Erika7
E
S
  • Question
Macro to Copy Info and Paste to another tab and then delete afterwards from original tab
Replies
0
Views
424
shawn2209
S
M
  • Question
Pull info from one sheet to another
Replies
4
Views
368
Fazza
F
T
  • Question
Matching info in 2 columns with another tabbed sheet and highlighting???
Replies
7
Views
297
FDibbins
FDibbins

Forum statistics

Threads
1,214,527
Messages
6,120,058
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top