Ask Your Question
0

Updating a spreadsheet on LibreCalc from supplier wholesale price list

asked 2020-03-18 05:06:54 +0100

russelld gravatar image

Hi All,

Looking for help on how to update a in-house order spreadsheet on LibreCalc from supplier price list.

The in-house order spreadsheet is used to calculate the total cost of the orders before the order is placed with the supplier.

Every time a supplier has a price change the internal in-house order spreadsheet has to be edited to reflect this. This takes a very long time as some sheets have 600+ rows. Also the manual process collects lots of errors from human data entry. Having a app to do this would be fantastic!

The in-house order spreadsheet has:

  • been edited from supplier spreadsheet by removing unstocked items and removing unnecessary columns of unrequired data
  • adds additional columns for item’s unique data for max and min shelf quantity, and notes
  • In-house order spreadsheet retains original sku from supplier wholesale price list.

The ideal process to update in-house order spreadsheet: 1. Download the supplier price list as spreadsheet 2. Manually edit supplier price list to remove unwanted data: ie remove unstocked items by deleting rows; delete unneeded columns of data 3. Use a macro or a some process available in LibreOffice to to export price and sku data from edited supplier price list and push into in-house order sheet to copy over prices for matching item sku

Note: the Process preserves the existing in-house data for max and min stock levels and notes

Hope this is possible with LibreOffice!

Thanks in advance for your help!

edit retag flag offensive close merge delete

Comments

Hello @russelld, I believe it is possible to perform this procedure, to give a suggestion, I need a copy of the file you download (if site, and free download, which one), your file with the modifications made.

Secrecy guaranteed, direct contact: https://wiki.documentfoundation.org/U...

Schiavinatto gravatar imageSchiavinatto ( 2020-03-18 12:45:37 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-08-25 17:27:46 +0100

newbie-02 gravatar image

imho there is a very simple way:

make supplier data available in one sheet, e.g. by 'link to external data',
(despite other comments i got it working for 'online data'),

have another sheet with:

  • in one column the 'item identifier' of the items to deal with,
  • in other columns your 'special data' like min/max stock etc.,
  • in other columns 'lookup copies' from the supplier sheet for the data relevant for you,

a sample for the third - just crafted, not tested:

=VLOOKUP($A5;$suppl_list.$A$1:$suppl_list.$GZ$409;5;0)

decoded:

$A5; - reference to the 'item identifier' in col. A, row number (5) not '$-ed' but left 'relative' to work in other rows as well, $suppl_list.$A$1:$suppl_list.$GZ$409; - range in the supplier list sheet to search for your items and it's properties,
('suppl_list': name of the sheet with the supplier data),
(range may be bigger than actual data),
(item identifier is expected in first column of that range, if not add a column with a copy)
5; - pull the value of the cell in the 5. column of the row found for the item reference, e.g. the price,
0 - 'mode', search for 'exact match', supplier list need not to be sorted, '1' would find 'near match' in sorted lists,

put some formulas like that together in one row for one item to pull all data needed, copy them to the range of items you want to observe ...

if the supplier's list is updated just update the 'supplier sheet', 'your' sheet should immediately reference the changes ...

if this answers your question pls. mark it by clicking the grey 'circled-v-hook' top left of it to turn it green,
if you like the answer pls. express that by 'upvoting' - the '^' sign above the number of votes,
if you dislike the answer pls. express that by 'downvoting' - the 'v' sign below the number of votes,

edit flag offensive delete link more

Comments

Hi newbie-02,

Thanks for the brilliantly elegant answer!

I added one extra field into the formula which was the last field for when VLOOKUP can't find the data.

=VLOOKUP(A2,$'Sheet3'.A2:I1669,9,FALSE())

Here FALSE() is for entries that can't be found in the updated price list and returns a #N/A This occurs as the supplier has deleted some items from the old list and the deleted item are omitted on the new updated list. Then its another function to sort these #N/A items into a list of "Items No Longer Stocked" Thanks again for your help!

russelld gravatar imagerusselld ( 2020-11-08 14:54:50 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-18 05:06:54 +0100

Seen: 94 times

Last updated: Aug 25 '20