Help with a Stock Control Database

You can do this with a user defined function (UDF).

Open the VBA editor (Alt F11) , add a new module by INSERT - MODULE

Paste in the following code.

Please Login or Register  to view this content.

return to the worksheet (Alt F11) and in D2 on the first sheet, type in =GetCurrentStockAmount(A2).

This can then be copied down to the other cells below as for any other function.

A non-macro solution is to put this formula into D2 and copy down:

=INDEX(INDEX('Stock Item Record'!G:G,MATCH(A2,'Stock Item Record'!C:C,0)+7):'Stock Item Record'!G148,MATCH("",INDEX('Stock Item Record'!G:G,MATCH(A2,'Stock Item Record'!C:C,0)+8):INDEX('Stock Item Record'!G:G,MATCH(A2,'Stock Item Record'!C:C,0)+20),0),0)

I hope that this does the trick.

Please Login or Register  to view this content.

I've used the leftmost two characters from the SKU code to target the correct sheet.

The problem is that you have multiple modules, all containing a function with the same name.

If you remove all modules except for Module 5, you should be OK.

Hi Martin
Ar yes thats done the trick works fine now. Thanks again
I know I will need to add some more sheets, will the Macro stil work?
Cheers

Provided that you stick to the current convention of naming the sheet with the first two letters of the SKU code, you should be OK. Please post back if this is not the case.

Try this.

Please Login or Register  to view this content.

Deleted post problem sorted

Hi

Try this which should deal with variation in the length of the record set.

Please Login or Register  to view this content.