Using Concatenate to Create Formulas

Hi,

I am trying to use the "CONCATENATE" Excel function to create formulas.

In cell A2, I have the value:
"file1"
In cell B2, I have the formula:
"=CONCATENATE("='C:\Users\njensen\Desktop\[B393 ",$A3,".xlsm]Recap & Subcontracts'!$K$43")"

The result of that formula would then naturally be:
"='C:\Users\njensen\Desktop\[B393 file1.xlsm]Recap & Subcontracts'!$K$43"

Granted, the "CONCATENATE" formula will just combine the strings, but if I copy the "CONCATENATE" cell and paste its contents as values into cell B7, it looks like a real formula but still does not evaluate anything.

The only way that I have found to actually make that formula evaluate anything is to physically click in the formula bar of cell B7 and then hit "ENTER".

This is great if I only have 1 or 2 cells that I need to do this with, but I have 100's. Is there some formatting trick that I can use so that all of the cells actually turn into formulas and evaluate instead of just look like formulas?

Thanks in advance and please see attached excel sheet for reference,
-Nate

Once you have pasted as values the CONCATENATE formulae, you could highlight them all and do Find & Replace (CTRL-H), and replace = with =.

Hope this helps.

Pete

Thanks Pete,
That works, I had hoped there was a faster way, but that will work.

Thanks FDibbins,
I tried using the indirect.ext function, but I couldn't get it to work for some reason (see my post here: http://www.excelforum.com/excel-form...ot-others.html)

Thanks again,
-Nate