Stuck in a circular formula

[Lebene]

Hello!

I am stuck in a circular formula, I understand why, I can't figure out the solution.

Basically it's a shop list which is sourcing the area manager, assistant area manager, regional manager and region code information from a reference table which can be easily updated when details change. In the index, match formulas, all of these fields are getting their correct details by matching against the AREA CODE.

My issue is that I wanted area codes to also automatically update in this shop list if changed in the reference table (just in case a shop gets rezoned), but since it's the point of reference for all the other fields, it's getting stuck in a circular error.

Any clue on how to fix this?

Thanks for any help or ideas

 

[J.Ty.]

In database language, you always need a key, i.e., a tag/label/piece of data which uniquely identifies a shop.

Initially it was AREA CODE and, as far as I understand, it worked well in this role. This permitted connecting the shop in the reference table and the same shop in the shop list. Again, in database language it was a foreign key in the list of shops.

However once you decide that AREAD CODE it is also subject to changes in the master table, you must devise a way to assure that this change is propagated to the shop list. This is doable, but you must change all instances of AREA CODE in all involved tables simultaneously, because if you do it in one place first, the connection between the two gets lost before you can update the other. This is why you are getting a cricular reference: your formulas are probably correct it the sense that they always attempt to make sure the other location is already updated before you upadate it here. Of course, such a task can never be completed, because you must update one of them first.

My suggestion is simple and somewhat strange: do not use normal cell editing for changes of AREA CODE in the master table, but use search-and-replace (Ctrl-H). Enter there the old AREA CODE, the new AREA CODE, make sure that you are doing replace in the whole workbook, and hit Replace all. If you do it this way, you do not need any additional formulas.

That's it.

J.Ty.

 

[Lebene]

In database language, you always need a key, i.e., a tag/label/piece of data which uniquely identifies a shop.

Initially it was AREA CODE and, as far as I understand, it worked well in this role. This permitted connecting the shop in the reference table and the same shop in the shop list. Again, in database language it was a foreign key in the list of shops.

However once you decide that AREAD CODE it is also subject to changes in the master table, you must devise a way to assure that this change is propagated to the shop list. This is doable, but you must change all instances of AREA CODE in all involved tables simultaneously, because if you do it in one place first, the connection between the two gets lost before you can update the other. This is why you are getting a cricular reference: your formulas are probably correct it the sense that they always attempt to make sure the other location is already updated before you upadate it here. Of course, such a task can never be completed, because you must update one of them first.

My suggestion is simple and somewhat strange: do not use normal cell editing for changes of AREA CODE in the master table, but use search-and-replace (Ctrl-H). Enter there the old AREA CODE, the new AREA CODE, make sure that you are doing replace in the whole workbook, and hit Replace all. If you do it this way, you do not need any additional formulas.

That's it.

J.Ty.

Hi J.Ty

Firstly, apologies for the sloooow reply, I have been quite swamped these past few weeks.

Yes, this seems like the best solution. Thankfully the area code is only used actively in that sheet and then rarely for staff who are not assigned to a shop but rather an area in the other sheet which is pulling through all this information. I will use the replace all in worksheet (making sure to match all content) if these shops or individuals ever see their area changing again.

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.