In Calc, items chosen from list using validity with defined range is not updating.

[MuhammadOsta]

Document contains many sheets, each one has a table where one of the columns contain entries from a list, this list is selected from a range using validity. this range is in sheet named prods as in:

a range called rawmat is E2:E300 contain raw materials names.

a range called rawmattable is E2:F300 contain raw materials names in column E and prices in column F.

The other sheet(s) has table where:

B7:B21 is assigned a validity list of range rawmat

E7:E21 is assigned a function: =IFNA(VLOOKUP(B7,rawmattable,2,0),)

(B7 in the function above change according to adjacent cell, it means lookup a match to B7 inside the range. IFNA used to ignore non found entries)

---

The problem is :

When I change a name of the raw material in the original sheet where the range is defined, the list gets updated alright when I want to choose from it but the already chosen items in the other tables isn’t updating.

I think Calc is assuming I have an item in cell that isn’t in the list, where changing the name in the original sheet means to it that it’s just a different item.

Please help me find a solution to make the item selected from list gets updated when I change it from the original place..

If you have a different method, please do advice. note that I already have over 40 sheets in the document, if you have an easy way to change all by macro that will be great, if not, I can change all manually this time before the document grows more. I would’ve uploaded the document for better understanding but it’s a company confidential information.

I appreciate you taking the time to help me.

Thank you.

[mikekaganski]

No solution, just a note.

Having a validity check assigned to a cell does not create a reference from this cell to another cell from the validity range. It’s just a filter applied at value insertion time, which passes or blocks assignment of an independent value to this cell.

So if you put a value to somesheet.B7, which is equal to prods.E3, it doesn’t mean that following changes to prods.E3 will affect somesheet.B7.

If your rawmat may change, you better create another - immutable - means to refer to your material inventory, like a number or an immutable string of characters (id) like 00000A123.

[MuhammadOsta]

thanks, that’s what I was afraid of… I was using name box for each product cell, then use that name in entries… anyway coworkers preferred the list thing

[JohnSUN]

You can’t correct the values ​​you have already entered, but you can easily find them

From Help

You can ease your task if you change the values ​​in rawmat not using editing, but using Find&Replace (don’t forget to set the “All sheets” checkbox)

[MuhammadOsta]

thank you. that’s good if I’m the only one editing the document, but it’s not practical for team work, it’s like old school open doc, edit and save. no version control for team collaboration. I’m looking for something more automated, I like using the name box for each product name and price (ex: prodA, prodA_P) all the time then use that for all sheets but other coworkers preferred the drop-down-list and this document is keep growing and improving in style and functions… soon I won’t be able to control it i.e. changes will be very confusing.

[JohnSUN]

If you feel that you can lose control, then you must be prepared for the fact that control will be lost very soon. You can try to limit other employees the ability to edit critical data and entrust this work to only one person. You can reconsider the choice of the tool - perhaps for your tasks you need a well-designed database, not a spreadsheet.

[MuhammadOsta]

yeah database is the best way to go but the company wants old school in-house document for this kind of sensitive info. I know how to build a good online program with database using Python but I don’t get to make that decision.

[JohnSUN]

I meant the “old school in-house database” using LibreOffice component Base.

An alternative solution - each employee uses his own spreadsheet and works with it in a way that is convenient for him, but always the same. In this case, summary information for the organization can be collected in a single place using the program (i.e. Python) - view each spreadsheet and collect data in a single repository.