Replace indirect formulas with direct cell references

[ahamilton]

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

 

[gaz_chops]

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

 

[steve the fish]

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

 

[ahamilton]

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

 

[Mackers]

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).

 

[gaz_chops]

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