Adding extra stock to a field in a master sheet

Pete W

Hi fairly new to working across sheets but have been using SS for a while.

I have a master sheet with stock in it and I want to update with an additional sheet which has deliveries in it.

I have carried out a Vlookup on the master and copied it to the delivery sheet and then added the stock item quantity to the delivery quantity in a new cloumn but how do I write this back to the original master sheet ?

Thanks in advance

Pete

Paul Newcome

You may end up with a circular reference using the VLOOKUP approach. My suggestion would be to get rid of the VLOOKUP pulling over the amount from the master to the form sheet and then use a SUMIFS in the master sheet to add up the total amount for each item. You are going to need to make new form entries for the starting stock (unless starting stock was zero) to ensure you have the appropriate amount.

=SUMIFS({Form Sheet Quantity Column}, {Form Sheet Item No Column}, @cell = [Item Code]@row)

Genevieve P.

Hi @Pete W

Would you be able to provide screen captures (either with dummy data or sensitive data blocked out)?

It sounds like you could have another column in the Master sheet with another VLOOKUP or INDEX(MATCH formula to bring in the delivery quantity.

Another option might be to create a Report and GROUP the rows together by the unique value so you can see information together that way.

Cheers,

Genevieve

Pete W

Hi thanks for you reply.

so the master sheet looks like this (theres a bit more to it but its not relevant to this)

The other sheet allows people to submit a form to add stock either by a form (as example below) or just pasting the information in for a bulk delivery.

The vlookup grabs the data from the master and adds the delivery quantity and works out the new stock level. I plan to have another sheet for people taking stock (although I could probably do it on one sheet).

So once i get the new value I need to write it over the original data and then i want to move this row to a another sheet that records all transactions.

Maybe this isn't the right approach but I thought it would be easy to write it back to the original sheet as an automation once the figure was populated.

Thanks again.

Regards Pete

Paul Newcome

You may end up with a circular reference using the VLOOKUP approach. My suggestion would be to get rid of the VLOOKUP pulling over the amount from the master to the form sheet and then use a SUMIFS in the master sheet to add up the total amount for each item. You are going to need to make new form entries for the starting stock (unless starting stock was zero) to ensure you have the appropriate amount.

=SUMIFS({Form Sheet Quantity Column}, {Form Sheet Item No Column}, @cell = [Item Code]@row)

Pete W

Hi thanks so much that works perfectly.

Paul Newcome

Happy to help. 👍️