Reference is not working as formula

[amruthubballi]

Hi,

I am trying to create a link cell in Column E by pasting values using paste special option. i.e =Sheet2!D10.

I am using concatenate function in Column D to get the required formula to be pasted special as values in column E by concatenating A3, B3, C3, A4, B4, C4, and so on.

The problem I am facing is that Column E is not automatically taking as a formula... it is just pasting the values as "=Sheet2!D10" To enable formula in column E I manually need to press F2 / Double click on a particular cell to activate reference for that cell.

So is there any trick or formula to add before "=Sheet2!D10" i.e on column E so that it can take the values of the cell directly from Sheet 2?

 

[shinigamilight]

=INDIRECT("'"&"Sheet2"&"'!"&"D6")

 

[etaf]

you dont need the =
=INDIRECT(B1&C1)

 

[jaeiow]

A possibility is to use text to columns on that last column with no delimiter, if that has worked with dates with me, it might work with formulas.

 

[amruthubballi]

Thank

A possibility is to use text to columns on that last column with no delimiter, if that has worked with dates with me, it might work with formulas.

Thank you Jaeiow... It has worked for me and I got the exact results. Out of all your reply was easiest.

 

[amruthubballi]

you dont need the =
=INDIRECT(B1&C1)

Thank you so much.. It has helped me.

 

[amruthubballi]

=INDIRECT("'"&"Sheet2"&"'!"&"D6")

Thank you so much... It has helped me to resolve the issue.