SOLVED

Stocksheet cells to update on invoice

Copper Contributor

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.

 

ProductStcok CodeQtyPrice Each
product 112311.00
product 2123422.00

 

The stock sheet has the following. I added the values as a drop-down list

 

ProductCodePrice
product 11231.00
Product 212342.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

9 Replies

@Davec62 

=IFERROR(INDEX($H$2:$H$23,MATCH($A2,$G$2:$G$23,0)),"")

You can try INDEX / MATCH as shown in the example.

update invoice.JPG

=IFERROR(INDEX($I$2:$I$23,MATCH($A2,$G$2:$G$23,0)),"")

This formula is in cell D2 and copied down.

@OliverScheurich

thanks very much for the reply. I will give it a try in the morning.

Dave

  

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

 

 

@OliverScheurich

@Quadruple_Pawn

 

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

best response confirmed by Grahmfs13 (Microsoft)
Solution

@Davec62 

poroduct.JPG

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.

data validation.JPG 

Thanks very much for your help, it is very much appreciated

Thanks

Dave

@OliverScheurich 

 

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

@Davec62 

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.

Hi

This has been a great help, thanks very much

Dave
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Davec62 

poroduct.JPG

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.

data validation.JPG 

View solution in original post