In Calc, linked data, How to auto add price of product in one sheet based on another data sheet

English
,
#1

Please help me find a method to make storage data entry populate from one sheet to another.

Sheet 1 called “product details” has in column A names of products and next to each cell the price in column B.

Other sheets each has table where i can enter list of products and quantities. I want the cell next to each product fill the price automatically so I can make the next cell the cost based on quantity I enter BUT read the price from sheet 1 as a linked data so it change on all sheets when making the change on sheet 1 “product details”.

What I’m currently doing is naming the filed of each cell in Sheet 1 for both name and value

Example:

A1 is “Prod01” in the cell reference name field, where the cell contain the long actual name of the product.

B1 is “Prod01_P” the price.

Now on the other sheets I use “=Prod01” where ever I want to enter the product name, then next cell “=Prod01_P”.

This is confusing because the document now contain over 30 sheets and is growing to about 70 sheets… I want something easier.

I tried making a rang list on the sheet1 then use validity on the other sheets to choose from list but this is still too much confusing and cells don’t end up changing when I change anything from Sheet 1.

Thank you for taking the time to help me out.

#2

Solved it, thanks all for helping.

Using your help and the help of a friend, I defined a range of the product details in sheet1, then at the other sheet use:
=IFNA(VLOOKUP(A1,rawmattable,2,0),)
where A1 is the cell of product name, then vlookup search from the range second column where the prices are written. the range is 2 columns.
note: to hide zeros, change the cell formatting to: [>0]0.000;[<0]-0.000;"";@
this will show any number in three digits after zero but hide if value is zero, works better than conditional formatting.

#3

Hello,

B1 on Sheet2 (and subsequent sheets): =VLOOKUP(A1;$Sheet1.A$1:B$9999;2;0) (assuming you have a maximum 9999 products in Sheet1

(Make a validity range for the product names, so to assure VLOOKUP to always find a correct value)

Note

  • You may prepare a more adapted formula using something like =IF(ISBLANK(A1);"";VLOOKUP(A1;$Sheet1.A$1:B$9999;2;0)) to show only a price (i.e. to avoid an error message), if a product is already selected.
  • You may also check for a solution using functions MATCH() and OFFSET() (=OFFSET($Sheet1.$A$1;MATCH(A1;$Sheet1.A$1:A$9999;0)-1;1))

Hope that helps.

#4

Love it! this is so useful, thank you so much! worked great except for manual cell entry that aren’t in the original sheet1 but I’ll find a way to make it ignore the error values.

#5

Can you please help add an “if” function to ignore product names written in cell that aren’t in the original sheet1 product details… or something that only returns the price if it found where it doesn’t do anything if not. I’ll keep trying to solve this but really appreciate your input. this will also help me a lot on an invoice document I use on regular basis. thank you

#6

Can you please help add an “if” function to ignore product names written in cell that aren’t in the original sheet1 product details

That’s why I proposed: (Make a validity range for the product names, so to assure VLOOKUP to always find a correct value). If doing so, the case you describe and you request a formula now, simply could not happen (an requires new products always to be added to Sheet1 in advance). This also would prevent from formulas getting more and more complex.

#7

The tables are more complex than what I described but I only wrote the part where I’m lost… part of the problem is notes are written in same column as product name. example: product1, product2, product3, after grinding: product4, after dry, product5 …etc up to 15 in total. I can work around by deleting the formula in the raw where I need to enter a non product name but I was hoping for more robust tables where no need to make changes depending on the data entered. also, I tried validity but didn’t work for the end calculation it gives N/A, do you have something else in mind to alter this? much appreciated ^^

#8

part of the problem is notes are written in same column as product name. example

Then even my formulas won’t work at all, since the use VLOOKUP(....;0) (or MATCH(...;0)) and the last parameter requires a full match. So my solution is almost worthless for your use case (though I find it very strange to enhance product with additional notes, which sound [for me] like various process steps - this make formulas much more complicated and you might need to turn regular expressions. I’d suggest to split off those details from the product names - but got to less understanding of the overall use case and data structures).

#9

You are right about the column it shouldn’t contain non original product data in the same place… but each table contain a different formulation, think of it like a recipe, where an important comment like “after dry” or other things aren’t always in same steps… sometimes after 2 products… other times after 8…etc it need to be that way for clarification and to fit for printing. I wonder if you can help me fix this by a logical function, something like: if product name is blank = do nothing, otherwise lookup for match to it then if match is blank = do nothing. or if cell A1 isn’t blank > lookup sheet1 range > if not found = blank. maybe something like this: but it still gives error: IF ISBLANK A1,"" IF ISBLANK (VLOOKUP(A1,$Sheet1.A$1:B$999,2,0)), VLOOKUP(A1,$Sheet1.A$1:B$999,2,0)

#10

Hi, this sounds to me like a simple problem that can easily be solved with VLOOKUP. I hope I understood your problem correctly. I have created a sample document for you. Here you can easily understand how to display the data related to the data in Sheet1.

If this answered your question, mark the question as answered. The little hook next to the answer.

How to auto add price of product in one sheet based on another data sheet.ods

#11

Wow! Thank you so much! I like how this works and it solves another automation problem I’m struggling with… I tried to do it on new document but it didn’t work the same… even after making the same validity option as you did… I’m going to use your document for something else and make the changes on it.

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.