# Stock list in XL03 - struggling with a formula

#### 917

##### New Member
I know this isn't the most traditional way to run a stock list, but essentially what I'm trying to do is fully reduce the current stock of leaflets to zero. No new ones will be added to the stock amount. I'm using Exel 2003 and Windows XP.

I've got two spreadsheets, Sheet A that colleagues use for tracking requests for leaflets. They enter the amount requested under the column that represents the ID number of the leaflet.

The other, Sheet B is a list of these leaflets. The description in column A and the ID in column B. Column C shows the current stock level, this has been manually entered and won't change.

I want cell D2 in Sheet B to take the amount entered in cell D2 on Sheet A and display this here. Whilst I've been able to sort this bit, it's the next bit I'm struggling with.

I then want cell E2 from Sheet A to show up D3, F2 from Sheet A to show up in D4 and so on. I
I've got approximately 30 rows of different leaflets so was hoping there was either some code I could use or there was some function I could use to replicate this.

Any help would be greatly appreciated!!!
Thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Words are too hard to see logic. Post a dummy spreadsheet with only 2 leaflets please.

Hi there, thanks for the reply.

It says I'm not allowed to post attachments....any ideas?

Cheers

copy the first few lines of each sheet and paste them in this reply box

Hi there

thanks again. here's the first few lines for sheet a

 Date Requested Team Who Requested Date Sent Out LL1 LL2 LL3 09/04/2013 A Tean 15/04/2013 12 6 10 10/04/2013 B Team 16/04/2013 10 32 0

<COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

and sheet b

 Description ID Stock Amount 15/04/2013 16/04/2013 17/04/2013 Leafelt 1 LL1 100 12 10 10 Leaflet 2 LL2 150 6 32 12 Leaflet 3 LL3 75 10 0 10

<COLGROUP><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" span=3 width=97><TBODY>
</TBODY>

Many thanks
917

 73 108 62 Date Requested Team Who Requested Date Sent Out LL1 LL2 LL3 09/04/2013 A Tean 15/04/2013 12 6 10 10/04/2013 B Team 16/04/2013 10 32 0 17/04/2013 5 4 3 18/04/2013 19/04/2013 20/04/2013 21/04/2013 22/04/2013 start Description ID ORIGINAL Stock Amount CURRENT stock amount 15/04/2013 16/04/2013 17/04/2013 18/04/2013 19/04/2013 20/04/2013 21/04/2013 22/04/2013 Leafelt 1 LL1 100 73 12 10 5 0 0 0 0 0 Leaflet 2 LL2 150 108 6 32 4 0 0 0 0 0 Leaflet 3 LL3 75 62 10 0 3 0 0 0 0 0 cell G18 OFFSET(\$E\$6,MATCH(G\$17,\$E\$7:\$E\$14,0),1) cell G19 OFFSET(\$E\$6,MATCH(G\$17,\$E\$7:\$E\$14,0),2) cell G20 OFFSET(\$E\$6,MATCH(G\$17,\$E\$7:\$E\$14,0),3) the lower table automatically updates when any leaflets are issued the upper table gives the current stock level

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

G17 is 15/4/2013 in lower table
G18:g20 are 12,6,10 in lower table
E7:E14 is the column of dates in upper table

Replies
3
Views
217
Replies
3
Views
335
Replies
14
Views
366
Replies
5
Views
338
Replies
0
Views
205

1,196,340
Messages
6,014,718
Members
441,838
Latest member
ykg1991

### 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.

### Which adblocker are you using?

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

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