Diiicult to do without VBA ?

keithkemble

Board Regular
Joined
Feb 15, 2002
Messages
160
My apologies for not being able to paste using HTML maker

What I am trying to do, without using VBA, is to be able to identify the last stock code in column B relative to the Location and current date

EG dynamically, the last stockcode for location 3 before the 5th Feb is 51025
Current date is $C$1

I can sumproduct using two conditions to establish the total qty for 2 conditions. Can this be adapted to use find ?


A B C D E
Date stock code Doc ref Location Qty in/(out)
2-Feb-05 12345 A1234 1 130
2-Feb-05 456 A1235 2 130
2-Feb-05 51025 A1236 3 130
2-Feb-05 10035 A1237 floor 130
2-Feb-05 25025 A1238 goodsin 130
2-Feb-05 12345 Out654 1 -10
4-Feb-05 12345 Out655 1 -15
5-Feb-05 12345 Out656 1 -15
3-Feb-05 51025 Out657 3 -50
14-Feb-05 51025 Out658 3 -25
2-Feb-05 25025 Out659 5 -16
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Is this what you want?


H4: =LOOKUP(2,1/((A2:A12< H1)*(D2:D12=H2)),B2:B12)
Book1
ABCDEFGH
1DatestockDocLocationQtyDate05-feb-05
202-feb-0512345A12341130Location3
302-feb-05456A12352130
402-feb-0551025A1236313051025
502-feb-0510035A1237floor130
602-feb-0525025A1238goodsin130
702-feb-0512345Out6541-10
804-feb-0512345Out6551-15
905-feb-0512345Out6561-15
1003-feb-0551025Out6573-50
1114-feb-0551025Out6583-25
1202-feb-0525025Out6595-16
Sheet5
 

keithkemble

Board Regular
Joined
Feb 15, 2002
Messages
160
Fairwinds
Thats exactly what I want.
Works a treat. Thanks a million
Its obvious really but it takes a genius to point it out
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

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
Top