This article gives you an example of where we may need to keep our formulas intact.

Suppose there are two tabs- Jan and Feb. And they both contain the same fields with exactly the same location. For e.g. Both Jan and Feb have 4 fields as shown below:

Now in the Summary tab, we want to bring in this information for both the months.
If you already have formulas in place for Jan and now you just want to fill up Feb numbers.

For Feb, you want Feb!B1 in cell C1 of summary. There is no point in linking all over again by going to Feb tab each time.
We want the same formulas as in Jan column but we just want to swap Jan with Feb.Like you have Jan!B1 in cell B1 of summary for Jan and we want Feb!B1 for Feb.

However, if we just copy the formulas from Jan to Feb, it will not stay the same but would change B to C.

In order to get around this, we need to do two things:
1) Freeze Jan formulas so that we can transport them to Feb.
2) Replace Jan with Feb.

In order to do the first step, just select the entire column B of the summary tab(Jan) and do “find and replace” and just replace = with any letter say s. This will keep our formulas intact.

Now just paste these in column C and then select column C and then using find and replace, just replace Jan with Feb.

 Once that is done, then select both  columns B and C, and replace back “s” with “=”.

And it is done as you can see below.

The above example can also be done using indirect function.Here is a link to the article which talks about “Indirect Function“.
 

Popularity: 28%