Using concatenate to create formula - displays formula instead of formula result.

[Maxjoseph]

I have a table of simple number values. I need to change each cell to carry it's current number multiplied by a separate reference cell. The table needs to contain live formulas, not hard-coded values. So for example:

Current contents of cell A3: 1436
Desired contents of cell A3: =1436*$A$1

My plan was to create a table alongside using concatenate, and then Paste Values back over the original table. I therefore set up two reference cells:

Content of reference cell X1: =
Content of reference cell X2: *$A$1

Which enabled me to create the first cell of my duplicate table starting at M1:

Content of duplicate cell M3: =CONCATENATE(X1,A1,X2)

This did indeed return what I wanted: =1436*$A$1


THE PROBLEM:

However when I copied this and pasted value into a new cell, it continued to be displayed as the formula, and not the result. I've tried changing the cell to various formats, but this has absolutely no impact; neither does toggling the Show Formulas button.

I can type the formula by hand into the adjacent cell with the same formatting and it displays the result perfectly.

What am I missing here?

Max.

 

[VoG]

Make sure that Calculation is set to Automatic.

 

[Maxjoseph]

Thank you VoG, but Calculation was already set to Automatic. Attempts at manual calculation haven't helped either.

 

[slinky]

Use indirect() instead of concatenate...

=INDIRECT(X1,A1,X2)

HTH,

slinky

 

[Maxjoseph]

Thank you slinky, but INDIRECT tells me that there my formula has too many arguments. I tried reading up further on INDIRECT but was unable to work out how it would apply to my problem.