Copy Formulas to Paste as Values to return # not text

[Kohai]

I am creating formulas that link to data in another
workbook. Because the data is not in continuous columns,
I have another cell at the top that tells me which column
the target data is in the target wbk. I concatenate a
formula to have it look like what I want and then copy
and paste as values to hopefully have the formula I
really want that returns the data I need.


For ex: Formula in D20 is ="='C:\sample\[test_book]
sheet1'!"&D$2&Row(D20)

In D2 is a Letter Column reference in the target book of
the data I want, say - G

so in the cell it looks like ='C:\sample\[test_book]
sheet1'!G20

If I copy this and paste in same cell as Value I get
'='C:\sample\[test_book]sheet1'!G20
The extra ' in the beginning prevents the actual # from
coming in. I have thousands of these and can't manually
remove all of the single quotes. Find/Replace doesn't
recognize it either.

I saw a short vba procedure that does work, but it takes
way too long. Is there a setting I can change so that it
doesn't paste/value as a string? The cell is formatted
as a number.

I know this is along one, but wanted to get as much info
as I could.

Thanks,

Kohai

 

[hgrove]

Kohai wrote...
...

For ex: Formula in D20 is
="='C:\sample\[test_book]sheet1'!"&D$2&Row(D20)

In D2 is a Letter Column reference in the target book of the
data I want, say - G

so in the cell it looks like ='C:\sample\[test_book]sheet1'!G20

If I copy this and paste in same cell as Value I get
'='C:\sample\[test_book]sheet1'!G20
The extra ' in the beginning prevents the actual # from coming in.

Tools > Options, select Transition tab, uncheck Transition formul
evaluation. Then reenter the formulas and paste them as values

 

[Kohai]

Thanks for the reply. Transition keys and formulas are
not checked, but the formulas still paste's as values
with the leading single apostrophe.

-----Original Message-----
Kohai wrote...
...

For ex: Formula in D20 is
="='C:\sample\[test_book]sheet1'!"&D$2&Row(D20)

In D2 is a Letter Column reference in the target book of the
data I want, say - G

so in the cell it looks like ='C:\sample\[test_book] sheet1'!G20

If I copy this and paste in same cell as Value I get
'='C:\sample\[test_book]sheet1'!G20
The extra ' in the beginning prevents the actual # from

coming in.

Tools > Options, select Transition tab, uncheck Transition formula
evaluation. Then reenter the formulas and paste them as values.

 

[Harlan Grove]

Thanks for the reply. Transition keys and formulas are
not checked, but the formulas still paste's as values
with the leading single apostrophe.

....

In XL97, transition formula evaluation is the only way I could replicate
your problem. I can't replicate it at all in XL2K. What Excel version are
you using?

Anyway, you may need a macro to do this. The following should work.

Sub unquote()
If TypeOf Selection Is Range Then
Selection.Formula = Selection.Value
End If
End Sub

Select the problem range and run it.

 

[Kohai]

Thanks for the code. I'm using xcl '02 and that does the
trick. I had tried another macro but it took too long to
process.