Stock list in XL03 - struggling with a formula

[917]

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

 

[oldbrewer]

Words are too hard to see logic. Post a dummy spreadsheet with only 2 leaflets please.

 

[917]

Hi there, thanks for the reply.

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

Cheers

 

[oldbrewer]

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

 

[917]

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

 

[oldbrewer]

			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>

 

[oldbrewer]

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