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
E2:E300 contain raw materials names.
a range called
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
E7:E21 is assigned a function:
(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.