We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2020-07-14 13:44:09 +0200

MuhammadOsta gravatar image

updated 2021-05-30 10:57:11 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete


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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-14 13:57:57 +0200 )edit

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

MuhammadOsta gravatar imageMuhammadOsta ( 2020-07-14 14:51:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-14 14:26:06 +0200

JohnSUN gravatar image

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)

edit flag offensive delete link more


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.

MuhammadOsta gravatar imageMuhammadOsta ( 2020-07-14 14:48:47 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2020-07-14 16:08:06 +0200 )edit

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.

MuhammadOsta gravatar imageMuhammadOsta ( 2020-07-14 18:33:53 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2020-07-14 18:44:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-14 13:44:09 +0200

Seen: 319 times

Last updated: Jul 14 '20