Is it possible to replace all the cell references in a formula at once?

[Carlitoexcel]

I am using excel 2016
[FONT=Roboto, Arial, sans-serif]I have a huge table with approximately 170 columns and 4900 rows. In this table I created a huge formula using "IFS", I need this formula to be in other columns but I need to change the reference for the formula to look at in order to make the calculations. I copy and paste the formula to the other columns but STILL I need to "change/replace" the reference one by one and because the formula is way to long it takes a long time for me to do this.
Is there a way to replace all the references at once? The formula is "using only one cell" for all the calculations; in other words the answer is based only by looking at one cell, but this cell needs to be replaced/changed every time I insert the formula to another column.
Thank you![/FONT]

 

[Gerald Higgins]

Hi, welcome to the board.

Your request is not very clear (at least to me).

Are you familiar with the use of the $ symbol in cell references in formulas ?
It sounds as if using the $ symbol, or changing the way you are using it, could be the answer here.

For example, if all versions of the formula must refer to cell A1, then try changing the reference to A1, to $A1, and then copy the formula to other columns.

On the other hand, if you want one formula to refer to A1, and the next column to refer to B1, then IF your formula contains $A1, consider changing this to A1.

 

[Carlitoexcel]

Hi, welcome to the board.

Your request is not very clear (at least to me).

Are you familiar with the use of the $ symbol in cell references in formulas ?
It sounds as if using the $ symbol, or changing the way you are using it, could be the answer here.

For example, if all versions of the formula must refer to cell A1, then try changing the reference to A1, to $A1, and then copy the formula to other columns.

On the other hand, if you want one formula to refer to A1, and the next column to refer to B1, then IF your formula contains $A1, consider changing this to A1.

Sorry that I didn't make myself clear. Yes I am familiar with $ to lock either the column or the row or both for a reference. The problem is that my formula is very long so let's say for example that I have the first formula referring to A1, and the second time I enter the formula into another column it refers to B1, and the third time it refers to C1 and so on, (keeping in mind that I do not have the formula reference locked with the $)

I know how to make it work in the following columns, and how to change the reference I just need a way to change all the references at once. Every time I change the formula to another column I still have to manually change the reference from for example A1 to B1, then from B1 to C1 and so on.

In my formula the reference A1 or B1 or C1, etc is present 19 times so I have to manually change it 19 times each time I enter it into a new column. So I want to know if there is a way that I can change all the 19 references at once. Once again luckily I on only need one cell for the reference but it repeats 19 times so it takes way to long when I am working with such a huge table. I have search everywhere and I can't find this, I hope there is a way because it is very inconvenient when the formula is too long and the reference repeats so many times. Thanks!!!!!

 

[philwojo]

Why don't you post your formula rather than talking in abstracts.

 

[Carlitoexcel]

Why don't you post your formula rather than talking in abstracts.

Okay I will post it if it helps but I don't think it will make a difference, I just need to change a reference that repeats 19 times in a formula all at once instead of manually changing the reference one by one 19 times.

 

[Carlitoexcel]

Here is my formula!
All I need to do is to be able to change all these 19 references "which are the same" all at once every time I use the formula in a different column.

Every time I use the formula in a different column I need my reference to change too.

I know how to change the reference by selecting the already existing reference and clicking on the new one but this takes too long to manually change all the 19 references one by one.

Is there a way to change all of them at once, only in a few steps???



=IF(OR([@[DJIA ($INDU) ]]=""),"",IFS([@[DJIA ($INDU) ]]<=-2.001%,"LOW",AND([@[DJIA ($INDU) ]]>=-2%,[@[DJIA ($INDU) ]]<=-1.495%),-4,AND([@[DJIA ($INDU) ]]>=-1.494%,[@[DJIA ($INDU) ]]<=-0.995%),-3,AND([@[DJIA ($INDU) ]]>=-0.994%,[@[DJIA ($INDU) ]]<=-0.495%),-2,AND([@[DJIA ($INDU) ]]>=-0.494%,[@[DJIA ($INDU) ]]<=-0.001%),-1,AND([@[DJIA ($INDU) ]]>=0%,[@[DJIA ($INDU) ]]<=0.494%),1,AND([@[DJIA ($INDU) ]]>=0.495%,[@[DJIA ($INDU) ]]<=0.994%),2,AND([@[DJIA ($INDU) ]]>=0.995%,[@[DJIA ($INDU) ]]<=1.494%),3,AND([@[DJIA ($INDU) ]]>=1.495%,[@[DJIA ($INDU) ]]<=2%),4,[@[DJIA ($INDU) ]]>=2.001%,"HIGH"))

 

[Gerald Higgins]

Personally I'm not sure I'm familiar with that type of reference - are these range names ? Or table elements ?

And can you describe exactly how you want these to change when you copy and paste the formula ?

 

[thisoldman]

You can turn off the use of structured table reference with File >> Options >> Formulas and removing the check mark next to "Use table names in formulas". This does not change any previously entered formulas that use the table references. But any new formula will then use the normal A1 cell references. It also will not affect any formulas you copy and paste. You'll need one copy of the formula without the table references for copy and paste purposes.

If you do decide to keep the option using table references, you can still write formulas with A1 style references by typing the references. Just don't Point to the cells when you're creating the formulas. It would be hard for me to break that habit, I'll tell you.

Dragging a formula to an adjacent column does change the column references; copy and paste does not.

 

[Carlitoexcel]

You can turn off the use of structured table reference with File >> Options >> Formulas and removing the check mark next to "Use table names in formulas". This does not change any previously entered formulas that use the table references. But any new formula will then use the normal A1 cell references. It also will not affect any formulas you copy and paste. You'll need one copy of the formula without the table references for copy and paste purposes.

If you do decide to keep the option using table references, you can still write formulas with A1 style references by typing the references. Just don't Point to the cells when you're creating the formulas. It would be hard for me to break that habit, I'll tell you.

Dragging a formula to an adjacent column does change the column references; copy and paste does not.

Thank you so much very helpful!!!, I will keep the "Use table names in formulas" because it makes it a little easier in this case, but very important to know this.

The DRAGGING instead of COPY AND PASTE worked great (it automatically uses the following cell as a reference instead of using the same reference when we use copy and paste)

Thanks again for sharing your knowledge and helping others, this will save me hours of work!

 

[Carlitoexcel]

Personally I'm not sure I'm familiar with that type of reference - are these range names ? Or table elements ?

And can you describe exactly how you want these to change when you copy and paste the formula ?

This are table elements. [@[DJIA ($INDU) ]] is just the name of that column. It is using that column as a reference.

In this case it is just using a name [@[DJIA ($INDU) ]], but if I remove that option as "thisoldman" showed me it will show for example [@[A]], it is the same thing.

My question was how to replace all of these references at once after I copy and paste them to another column. For example: "maybe when they are in the new column I can select all of the references [@[DJIA ($INDU) ]] only, and then click on the new reference, so all the old references will be replaced by the new reference. It takes too long to manually replace all of them one by one (it is repeated 19 times)