Both reference ranges in a column formula are not globally updating? Any advice?

Jef Forward

Hello All, I have edited a column in my template with a new formula. This column NOW has a formula that pulls in data from a reference sheet (metadata sheet). I have tested this out through a provisioning test, and it works! I Love it! This is a screen shot from my template sheet.

=INDEX({Metadata Range 1}, MATCH("Project Code", {Metadata Range 2}, 0))

My challenge is updating my other "already provisioned projects" to this formula. I have gone through the process of the update, and the formula is updated, however, It only references (1) of the (2) range references, instead of both references. This is a screen shot from a project sheet.

Any ideas how to global fix this, or should I accept the time commitment and boredom of a manual correction? Any suggestions would be very helpful! Thanks

Paul Newcome

This is going to be a manual process. I am assuming that the one reference that is currently working is already being used elsewhere on the sheet which means the reference was already created for that sheet before the formula was added whereas the reference that is not working is a brand new reference.

Think of a global update as simply adding a text string the same as manually typing something. If you type out a previously created {existing reference} when manually entering a formula, it will work. But if you type out a {new reference} without following the proper steps to create it, you get an error. It is the same concept with Global Updates. You are simply entering a text string. The Global Update does not actually CREATE a new reference.

Paul Newcome

This is going to be a manual process. I am assuming that the one reference that is currently working is already being used elsewhere on the sheet which means the reference was already created for that sheet before the formula was added whereas the reference that is not working is a brand new reference.

Think of a global update as simply adding a text string the same as manually typing something. If you type out a previously created {existing reference} when manually entering a formula, it will work. But if you type out a {new reference} without following the proper steps to create it, you get an error. It is the same concept with Global Updates. You are simply entering a text string. The Global Update does not actually CREATE a new reference.

Jef Forward

Thank you Paul - I understand now. Jef

Paul Newcome

Happy to help. 👍️