Stock lists - help with formula (XL03)

[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 in 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.

I've attached a spreadsheet as this may be easier to see rather than my explanation above.
Book1.xlsx

Any help would be greatly appreciated!!!
Thanks

[benishiryo]

hi 917, welcome to the forum. try this in D2:
=INDEX('Sheet A'!$D$2:$F$6,MATCH(D$1,'Sheet A'!$C$2:$C$6,0),MATCH($B2,'Sheet A'!$D$1:$F$1,0))

[Tony Valko]

Here's another one...

Entered in D2:

=VLOOKUP(D$1,'Sheet A'!$C$2:$F$6,MATCH($B2,'Sheet A'!$C$1:$F$1,0))

Copy across to H2 then down to D4:H4.

[917]

Thanks for the welcome guys and thanks for the formulas.

I've managed to get them to work however, if I wanted to add additional columns (say other requests have come in on different dates) how would I go about doing this? I can't seem to change the formula to replicate this without getting #NA errors.

Also, the same goes for adding additional rows for other leaflets.

I'm probably being really thick, but appreciate all the help. Cheers

[Tony Valko]

If you want to add more columns then you just need to change the references to include the new range.

Can you post an updated file to show us exactly what you mean?