Nov 01 2022 04:16 AM
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
Nov 01 2022 11:28 AM
=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.
Nov 01 2022 11:51 AM
Nov 02 2022 02:52 AM - edited Nov 02 2022 02:56 AM
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
Nov 02 2022 04:25 AM
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
Nov 02 2022 05:07 AM
SolutionIn 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.
Nov 02 2022 06:07 AM
Nov 29 2022 08:27 AM
Hi
sorry but I cannot get products part to work. I have attached the test sheets, really not sure where I am going wrong. If you can help it would be greatly appreciated.
Thanks
Dave
Nov 29 2022 09:09 AM
Hi, in the attached file i've converted the table in the "Stock Sheet" worksheet into a dynamic table. The dropdowns in the "Invoice" worksheet don't show empty cells anymore.
Nov 30 2022 01:45 AM
Nov 02 2022 05:07 AM
SolutionIn 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.