Replace absolute cell reference with Indirect cell reference in formula

[Roothy]

Hello all,

I've been looking for a way to get my excel workbook somewhat fool-proof (impossible, probably).
It is an workbook ranging 5 sheets in which 1000 lines of code need to be rearranged and adapted to come up with something my program will understand.
In the pursuit of making it fool proof, I found the INDIRECT formula to help protect against cut/copy/paste errors and I want to incorporate it in all my formulas, but doing it manually will require me to go over 14 columns with 1000 rows.
I made an exert in a new workbook, see attachment.
In cell L3, I started with replacing the cell reference with A3 into (INDIRECT("'Structure'!A3")),
M3 from =IF(G3="NLP",B3,("'"&B3&"'")) into =IF((INDIRECT("'Structure'!G3"))="NLP",(INDIRECT("'Structure'!B3")),("'"&(INDIRECT("'Structure'!B3"))&"'"))
ETc etc, completely for the range L3:W1003.

But after completing the first line, autocomplete couldn't help me because the reference is between quotation marks, so it doesn't continue a count.
Macro recording didn't get what I was doing, so couln't complete it either. Using Macro's already online all tried putting INDIRECT function in front of the formula, but also that results in a REF! error.

Basically, I'm a little lost and spent my entire day on just this issue...

Hopefully you can help me out, give me a solutions, an alternative or w/e.

[TMS]

Maybe:

="'"&(INDIRECT("'Structure'!A"&ROW(L3)))&"'"

and
=IF((INDIRECT("'Structure'!G"&ROW(M3)))="NLP",(INDIRECT("'Structure'!B"&ROW(M3))),("'"&(INDIRECT("'Structure'!B"&ROW(M3)))&"'"))

etc.


Regards, TMS

[Roothy]

Hey TMS,

Thanks for your reply.
I tried to code you suggested but it failed/returned a REF! error. Probably because the reference to the sheet and cell is between quotation marks?
I'm still working on sorting through the evaluation thing, to see where it fails precisely, but it what i mentioned above seems to be the first part to return a REF! error.

[TMS]

You said you wanted to be able to drag the formulae down. The constructs above allow that to happen. However, what it finds in the cell is what it finds in the cell. I suggest you use Evaluate Formula to see what the formula tries to do.

It would probably help if you gave some actual data and the static formulae that work.

Regards, TMS

[Roothy]

Hey TMS,

Thanks again, I think I must be the one doing something wrong then. I'm going to retry and see what I'm doing wrong with pasting your formula. (hard to make things fool-proof against yourself :P )
I don't think I have the file on this computer, so I'll get back to it. If you say it should work, then I'm sure you'll be right.

Best,

[Roothy]

Hey TMS,

As expected, you were completely right. I wasn't looking correctly anymore, yesterday.
I pasted it again (in the right cell this time) and it worked splendidly.
You saved me a ton of work, thank you!

Best,
Roothy

[TMS]

You're welcome. Thanks for the rep.