The other problem is that my suppliers spreadsheet is in no particular order. different variations of the same product can be at different ends of the spreadsheet. Sync sheets in Libreoffice - Super User gives an example of what I would like
@Paulmcf87 Not sure you saw what I wrote about possibly using ‘vlookup’. The only common ground between your table and the suppliers is the “Title”. Even in Base you need a common field to ‘Link’ items in one table to items in another. So right now your link is the Title. As for supplier info in no order, vlookup can be set for that. Chapter 13 of the LO Calc docs (found here) discusses vlookup.
@Ratslinger I will need to have a look into vlookup, not sure how it works.
I was under the impression that with databases, linking tables could be used. Would vlookup be a similar idea?
sorry. linked the wrong question above… a more detailed version of whjat I am looking for can be seen here… Sync sheets in Libreoffice - Super User
vlookup is fairly simple to use. This is possibly the easiest route. With databases, as I stated, you CAN link one table to another - you just need a common field for the link (actually a relationship). You will find the same is true with vlookup - using a cell value in your information the supplier information is searched for a match & when found the appropriate secondary cell data is returned. Once you set this up it should be just dropping in new supplier file & your data is updated.
@Ratslinger
Thank you for taking the time to create that spreadsheet to demonstrate and I can definitely see, from playing around with it, how it could work.
My main issue, though, is that my suppliers spreadsheet has no order to it…
for example if I download it today product 1 variant 2 could be on line 2 but tomorrow it could be on line 22.
I wish their spreadsheets where better organised lol
So ideally, I need something where the sheet recognises the products unique id and then knows take the corresponding value from, say, column d to populate the data.
so I am matching terms by ID as opposed to Cell
@Ratslinger had another play around. it seems the only way to search is via Column number. for this to work for me, I would need to be able to search via Rows so I can select Row D for example as being the row which holds the relevant data
@Paulmc87 I really don’t understand you last few comments only because all of what you stated already works plus more you don’t realize. A) Look at the documentation mentioned; B) Sequence, table size, and even blank lines does not affect the results - Have you even tried it because I did before posting; C) If the Title isn’t unique (and you want to search on something else) you’ll have to add that field to your info & use that for searching; D) It already returns row information.
So, my question is - If something isn’t working, what is it and how did you test to show it doesn’t work? I’ve already given you a working sample to test with.
@Ratslinger would like to thank you first off for all your help :-)… So if I link the two spreadsheets using Vlookup. then download an up to date spreadsheet from my supplier, with the title cells in the same position but where the stock details may be in different areas, IE product A moved from line 2 to line 22… the vlookup will realise that the data that it referred to on line 2 is now on line 22?
@Paulmcf87 YES, YES,YES! I have stated this multiple times. But again, you can prove it to yourself with the test file sent. For example - Sheet3 (supplier data) MOVE (not just copy) line 2, for example, and place it on line 10. Then change the Stock or Price value. Now look at the result in Sheet2. It will find it in the newly placed spot and has the correct updated info. This is basic testing.
I posted an answer on your SuperUser topic. I really don’t think LO is the right tool for this job, and is certainly unnecessary if you’re using WP All Import plugin.
What you want to do here is update database table columns when the same columns in another table update and you want to tie the two columns based on the variation ID. (one ID used by WC, the other by your supplier) With much fuss and effort you ‘might’ be able to accomplish the task with LO Calc, it’s just not necessary given the workflow you already have.
As a follow-up to Adrien’s response concerning whether '“LO is the right tool for this job”; LO can be the right tool. I believe that you have a structural problem. The purpose of a database is to store information. The purposed of a spreadsheet is to evaluate scenarios. So your data should be stored in a database. Changes to the database and/or filtered results, would then be evaluated by the spreadsheet. Essentially your structure is backwards.
@SteveR
I have since outed to use purely spreadsheets and use a vlookup.
Would there be a more user friendly/simple option available using spreadsheets and DBs together?
First, you should examine how your supplier stores their data. The implication from your post is that the suppler has it as a spreadsheet, which would be unfortunate. In any event, you would need to create a database that mimics the suppliers fields. This would include a logical field (established by you) to identify which products you sell and which you don’t. After downloading the supplier information, you would need to import the suppliers information into the database. (see next comment).
Don’t worry about importing irrelevant data from the supplier. That issue is solved by the logical field identifying which products you sell and which you don’t. Subsequent database queries, based on that logical field would give you a list of your products and quantities available. (This is a brief outline of the approach required, not a complete solution.)
Good question about database and spreadsheet, there’s so much we can do with that that we’d need a whole article to dig more into it.
Actually our team at Zenkit dug into this topic and wrote and article on our blog, we’d love to have your feedbacks:
This answer and link provides no solution to the actual question!