# Replace indirect formulas with direct cell references

#### ahamilton

##### New Member
Hi,

I feel that there is an efficient way to do this but I can't work it out!

I have a large number of indirect cell references which I would like to replace with the cell reference which they solve for (e.g. ='sheet 1'!A1) But I can't work out an efficient way of doing this. I focussed on formulatext + paste value, to no avail.

My indirect formula is:
=-IFERROR(INDEX(INDIRECT("'"&J\$4&"'!m7:m1000"),MATCH(\$C47,INDIRECT("'"&J\$4&"'!K7:k1000"),0)),0)

There are a small number of:
=INDIRECT("'"&\$P\$61&"'!"&CONCATENATE(INDEX(W\$63:W\$67, MATCH(\$L80, \$N\$63:\$N\$67, 0)),\$N80))

I am able to replace them by hand, will take 4 hours but I am worried that it will have a large risk of error and would like a formulaic solution?

Thoughts appreciated,

A

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try Find & Replace, you will need to do it in a few steps.
1st step would be to highlight the range that has the formula, use Ctrl H to find & replace, Find = and change to '=, that will change them all to Text.
Then change the relevant parts of the formula.

Last step, change the '= back to =, to change back to formula.

hth

Gaz

You dont really need to change them to text. As long as the formula remains valid it will replace eg:

Find what: INDIRECT("'"&J\$4&"'!m7:m1000")
Replace with: Sheet1!m7:m1000

THanks, I think my problem is that I would like to have a straight cell reference =a1, your suggestion, though helpful, would still leave an index match (I need this to be dummy proof as being used by very basic operators) Any thoughts? Thank you. A

Try Find & Replace, you will need to do it in a few steps.
1st step would be to highlight the range that has the formula, use Ctrl H to find & replace, Find = and change to '=, that will change them all to Text.
Then change the relevant parts of the formula.

Last step, change the '= back to =, to change back to formula.

hth

Gaz

If I'm understanding correctly, you want to change the match to permanently reference the matched cell, undynamically. So if the result was cell M600 on Sheet3, your formula is =Sheet3!M600

You can do this by splitting out the match into a separate column =MATCH() then adding 3, concatenating the sheet name with ! and your match (you will have to add to the match number as you aren't matching from row 1).

I think you would need a bit of VBA to accomplish it.

Replies
6
Views
402
Replies
1
Views
239
Replies
7
Views
395
Replies
8
Views
175
Replies
2
Views
79

1,207,286
Messages
6,077,533
Members
446,288
Latest member
lihong3210

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back