Forum Discussion
Stocksheet cells to update on invoice
Hi
I have stock sheet with a list of products. There are only 26 on the test sheet. I would like to reference the cells in an invoice.
The products have a name, stock code and price. I added the fields as a drop-down to the invoice which is fine. The issue for me that you have to select the product then the code then the price. The ideal scenario would be that once a product is selected (product 1) the code and price are automatically populated.
| Product | Stcok Code | Qty | Price Each |
| product 1 | 123 | 1 | 1.00 |
| product 2 | 1234 | 2 | 2.00 |
The stock sheet has the following. I added the values as a drop-down list
| Product | Code | Price |
| product 1 | 123 | 1.00 |
| Product 2 | 1234 | 2.00 |
So do I need to link them in the stock sheet first, if so how? Is what I am trying to achieve possible?
Thanks
Dave
In the attached file there is a defined name "Product". This defined name refers to column A (column name is "Product") in sheet "Stock Sheet". In the screenshot the german "Bezieht sich auf" means "refers to".
This defined name is entered as source list for data validation.
=IFERROR(INDEX($H$2:$H$23,MATCH($A2,$G$2:$G$23,0)),"")You can try INDEX / MATCH as shown in the example.
=IFERROR(INDEX($I$2:$I$23,MATCH($A2,$G$2:$G$23,0)),"")This formula is in cell D2 and copied down.
Hi I have the stock sheet on a separate tab. The match part needs to include that.
My initial post probably wasn't very clear.
The products are selected from a dropdown, when this is done I would like the stock code and price to be automatically populated. The product list will increase in size over time. I have added a screenshot to view of the sheets. INVOICE TABLE
I appreciate your help!
Thanks
Dave
Hi
I have managed to get the formula you gave me to work with the separate tab for the stock sheet. I also need the stock code to automatically populate as well.
=IFERROR(INDEX('Stock Sheet'!$C$2:$C$100,MATCH($A14,'Stock Sheet'!$A$2:$A$100,0)),"")
I added a product as test but this is not pulled in. I had to go back in and go to the Data Validation Tab again. Is there a way to avoid this please apart from selecting a large amount of empty cells?
Apologies for all of the additional posts.
Thanks
Dave
