Copying cell with input range to different worksheet

[Guest]

I am trying to copy a workbook from one worksheet to another. The workbook
contains cells with input ranges referencing a workbook within the original
worksheet. When I copy or move to the second worksheet the Input Range
formula changes
from
'Valid Values'!$W$1:$W$3
to
'[OriginalWorksheetName.xls]Valid Values'!$W$1:$W$3

obviously referencing the original spreadsheet. Is there a way for this to
copy in a non relative way?

 

[Peo Sjoblom]

If it is just a single cell copy from the formula bar, if there are many
replace = with ^=^ get all formulas converted to text strings, copy and
paste then reverse the replace in both workbooks
and replace ^=^ with =


--


Regards,


Peo Sjoblom

 

[Guest]

There are no "=" signs in the formula. The formula is embedded in the input
range macro. I get to the formula "'Valid Values'!$W$1:$W$3 by selecting
the cell in question, right click then select Format Control, the formula is
located under the Control Tab under the category Input Range.
--
Doug T

If it is just a single cell copy from the formula bar, if there are many
replace = with ^=^ get all formulas converted to text strings, copy and
paste then reverse the replace in both workbooks
and replace ^=^ with =


--


Regards,


Peo Sjoblom

I am trying to copy a workbook from one worksheet to another. The workbook
contains cells with input ranges referencing a workbook within the
original
worksheet. When I copy or move to the second worksheet the Input Range
formula changes
from
'Valid Values'!$W$1:$W$3
to
'[OriginalWorksheetName.xls]Valid Values'!$W$1:$W$3

obviously referencing the original spreadsheet. Is there a way for this
to
copy in a non relative way?

 

[Gord Dibben]

Select the formula cells in source sheet and Edit>Replace

What: =

With: ^^^

Replace all.

Copy to target worksheet and reverse the Edit>Replace on both sheets.


Gord Dibben MS Excel MVP

 

[Guest]

See my reply to Peo. Clearly I did not word the original question well. I
think the issue is this is a macro and I am not a macro expert (understand
but don't use extensively)
--
Doug T

Select the formula cells in source sheet and Edit>Replace

What: =

With: ^^^

Replace all.

Copy to target worksheet and reverse the Edit>Replace on both sheets.


Gord Dibben MS Excel MVP

I am trying to copy a workbook from one worksheet to another. The workbook
contains cells with input ranges referencing a workbook within the original
worksheet. When I copy or move to the second worksheet the Input Range
formula changes
from
'Valid Values'!$W$1:$W$3
to
'[OriginalWorksheetName.xls]Valid Values'!$W$1:$W$3

obviously referencing the original spreadsheet. Is there a way for this to
copy in a non relative way?