Auto assigning sale item invoice number against stock list

I have attached a spreadsheet to help explain what i am trying to achieve. You would need to open the spreadsheet to understand my question.


I would like excel to be able to automatically assign the "Invoice no" from the sales sheet into the stock sheets "111" and "222" as appropriate.

Also the invoice number would need to be assigned at the lowest empty stock cell.



For the solution i think the answer would not be a formula.

A solution that would be very help could be a macro where i select the invoice number and activate the macro with a shortcut when will then assign the invoice number against the items. This would be done individually per each invoice number.


This may be an unusual request, but your help on this one would be much appreciated.

I look forward to your solutions.

Thanks