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?
--
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



"Doug T" wrote in message
...
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?
--
Doug T

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


"Peo Sjoblom" wrote:

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



"Doug T" wrote in message
...
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?
--
Doug T

Select the formula cells in source sheet and EditReplace

What: =

With: ^^^

Replace all.

Copy to target worksheet and reverse the EditReplace on both sheets.


Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 07:20:01 -0700, Doug T
wrote:

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?

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


"Gord Dibben" wrote:

Select the formula cells in source sheet and EditReplace

What: =

With: ^^^

Replace all.

Copy to target worksheet and reverse the EditReplace on both sheets.


Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 07:20:01 -0700, Doug T
wrote:

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?