INDIRECT Function impact on Copy Worksheet

> ... a copy "values only" of a complete worksheet

If you do an entire source sheet copy > paste special > values > ok over to
a new sheet in a new book, think you shouldn't be getting any error values
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> I am trying to do into a new
> workbook. However, the new copy has #REF in each cell where the original
> workbook referenced the INDIRECT function. Is there any way around this?
>
> Also, I get a #NAME in the new copy for each cell that had a used defined
> function in the original worksheet.
>
> Thanks, BG

Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the '(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG


"Max" wrote:

> > ... a copy "values only" of a complete worksheet
>
> If you do an entire source sheet copy > paste special > values > ok over to
> a new sheet in a new book, think you shouldn't be getting any error values
> ...
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> > I am trying to do into a new
> > workbook. However, the new copy has #REF in each cell where the original
> > workbook referenced the INDIRECT function. Is there any way around this?
> >
> > Also, I get a #NAME in the new copy for each cell that had a used defined
> > function in the original worksheet.
> >
> > Thanks, BG
>
>
>

The thoughts given earlier were specific to your orig. post's line:
> ... a copy "values only" of a complete worksheet

Are you still wanting to copy "values only" ?
The copy method which you describe below copies everything, including
formulas which is why you're facing all those errors

To copy "values only" of a complete worksheet,

Select the entire sheet (press CTRL +A)
Right-click > Copy

In a new Sheet1 in a new book,
Right-click on A1 > Paste special > Check "Values" > OK

If you need to copy over the formats as well, then just do one more:
Right-click on A1 > Paste special > Check "Formats" > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> Hi Max,
>
> Thank you for your reply. Please try the following.
>
> Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
cell
> A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
Sheet1
> and note the words 'This is a TEST!' in cell A1. Then right-click on
Sheet1
> tab and click on the "Move of Copy..." option. Set "To book:" to the
'(new
> book)' selection, check the "Create a copy" box and click OK.
>
> In the new workbook created after the OK, you should see a #REF in cell A1
> of Sheet1. This is my problem.
>
> Also, if you replace the INDIRECT with the name of a USER defined VBA
> function, you will see #NAME rather than #REF. This is my other problem.
>
> In advance, thank you for your help!
>
> BG

Hi Max,

Again, thank you for your help and I understand your comments. However,
when I

"Right-click on A1 > Paste special > Check "Values" > OK"

I do not see the "normal" Paste Special options of All, Formulas, Values, etc.

I see a Paste Special window with:

Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
and "Paste link:" radio buttons and a "As:" window with

Microsoft Excel Worksheet object
Picture
Bitmap
etc.

I am running Excel 2002 (10.6501.6735) SP3

Again, thank you.

BG

"Max" wrote:

> The thoughts given earlier were specific to your orig. post's line:
> > ... a copy "values only" of a complete worksheet
>
> Are you still wanting to copy "values only" ?
> The copy method which you describe below copies everything, including
> formulas which is why you're facing all those errors
>
> To copy "values only" of a complete worksheet,
>
> Select the entire sheet (press CTRL +A)
> Right-click > Copy
>
> In a new Sheet1 in a new book,
> Right-click on A1 > Paste special > Check "Values" > OK
>
> If you need to copy over the formats as well, then just do one more:
> Right-click on A1 > Paste special > Check "Formats" > OK
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> > Hi Max,
> >
> > Thank you for your reply. Please try the following.
> >
> > Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
> cell
> > A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
> Sheet1
> > and note the words 'This is a TEST!' in cell A1. Then right-click on
> Sheet1
> > tab and click on the "Move of Copy..." option. Set "To book:" to the
> '(new
> > book)' selection, check the "Create a copy" box and click OK.
> >
> > In the new workbook created after the OK, you should see a #REF in cell A1
> > of Sheet1. This is my problem.
> >
> > Also, if you replace the INDIRECT with the name of a USER defined VBA
> > function, you will see #NAME rather than #REF. This is my other problem.
> >
> > In advance, thank you for your help!
> >
> > BG
>
>
>

Really don't know what's happening over there <g>, but as a last attempt,
try the alternative to right-clicking on A1 in the new sheet as per below
(my ver is Excel 97)

(Do hang around awhile here for better insights from others ..)

In a new Sheet1 in a new book,

Instead of
> Right-click on A1 > Paste special > Check "Values" > OK

With A1 selected,
click Edit > Paste special > check "Values" > OK

If you need to copy over the formats as well, then just do one more:

Instead of
> > Right-click on A1 > Paste special > Check "Formats" > OK

With A1 selected,
click Edit > Paste special > check "Formats" > OK

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:F3D079A3-7C83-4386-9CA1-E53CF02A2878@microsoft.com...
> Hi Max,
>
> Again, thank you for your help and I understand your comments. However,
> when I
>
> "Right-click on A1 > Paste special > Check "Values" > OK"
>
> I do not see the "normal" Paste Special options of All, Formulas, Values,
etc.
>
> I see a Paste Special window with:
>
> Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
> and "Paste link:" radio buttons and a "As:" window with
>
> Microsoft Excel Worksheet object
> Picture
> Bitmap
> etc.
>
> I am running Excel 2002 (10.6501.6735) SP3
>
> Again, thank you.
>
> BG

> ... a copy "values only" of a complete worksheet

If you do an entire source sheet copy > paste special > values > ok over to
a new sheet in a new book, think you shouldn't be getting any error values
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> I am trying to do into a new
> workbook. However, the new copy has #REF in each cell where the original
> workbook referenced the INDIRECT function. Is there any way around this?
>
> Also, I get a #NAME in the new copy for each cell that had a used defined
> function in the original worksheet.
>
> Thanks, BG

Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the '(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG


"Max" wrote:

> > ... a copy "values only" of a complete worksheet
>
> If you do an entire source sheet copy > paste special > values > ok over to
> a new sheet in a new book, think you shouldn't be getting any error values
> ...
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> > I am trying to do into a new
> > workbook. However, the new copy has #REF in each cell where the original
> > workbook referenced the INDIRECT function. Is there any way around this?
> >
> > Also, I get a #NAME in the new copy for each cell that had a used defined
> > function in the original worksheet.
> >
> > Thanks, BG
>
>
>

The thoughts given earlier were specific to your orig. post's line:
> ... a copy "values only" of a complete worksheet

Are you still wanting to copy "values only" ?
The copy method which you describe below copies everything, including
formulas which is why you're facing all those errors

To copy "values only" of a complete worksheet,

Select the entire sheet (press CTRL +A)
Right-click > Copy

In a new Sheet1 in a new book,
Right-click on A1 > Paste special > Check "Values" > OK

If you need to copy over the formats as well, then just do one more:
Right-click on A1 > Paste special > Check "Formats" > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> Hi Max,
>
> Thank you for your reply. Please try the following.
>
> Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
cell
> A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
Sheet1
> and note the words 'This is a TEST!' in cell A1. Then right-click on
Sheet1
> tab and click on the "Move of Copy..." option. Set "To book:" to the
'(new
> book)' selection, check the "Create a copy" box and click OK.
>
> In the new workbook created after the OK, you should see a #REF in cell A1
> of Sheet1. This is my problem.
>
> Also, if you replace the INDIRECT with the name of a USER defined VBA
> function, you will see #NAME rather than #REF. This is my other problem.
>
> In advance, thank you for your help!
>
> BG

Hi Max,

Again, thank you for your help and I understand your comments. However,
when I

"Right-click on A1 > Paste special > Check "Values" > OK"

I do not see the "normal" Paste Special options of All, Formulas, Values, etc.

I see a Paste Special window with:

Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
and "Paste link:" radio buttons and a "As:" window with

Microsoft Excel Worksheet object
Picture
Bitmap
etc.

I am running Excel 2002 (10.6501.6735) SP3

Again, thank you.

BG

"Max" wrote:

> The thoughts given earlier were specific to your orig. post's line:
> > ... a copy "values only" of a complete worksheet
>
> Are you still wanting to copy "values only" ?
> The copy method which you describe below copies everything, including
> formulas which is why you're facing all those errors
>
> To copy "values only" of a complete worksheet,
>
> Select the entire sheet (press CTRL +A)
> Right-click > Copy
>
> In a new Sheet1 in a new book,
> Right-click on A1 > Paste special > Check "Values" > OK
>
> If you need to copy over the formats as well, then just do one more:
> Right-click on A1 > Paste special > Check "Formats" > OK
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> > Hi Max,
> >
> > Thank you for your reply. Please try the following.
> >
> > Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
> cell
> > A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
> Sheet1
> > and note the words 'This is a TEST!' in cell A1. Then right-click on
> Sheet1
> > tab and click on the "Move of Copy..." option. Set "To book:" to the
> '(new
> > book)' selection, check the "Create a copy" box and click OK.
> >
> > In the new workbook created after the OK, you should see a #REF in cell A1
> > of Sheet1. This is my problem.
> >
> > Also, if you replace the INDIRECT with the name of a USER defined VBA
> > function, you will see #NAME rather than #REF. This is my other problem.
> >
> > In advance, thank you for your help!
> >
> > BG
>
>
>

Really don't know what's happening over there <g>, but as a last attempt,
try the alternative to right-clicking on A1 in the new sheet as per below
(my ver is Excel 97)

(Do hang around awhile here for better insights from others ..)

In a new Sheet1 in a new book,

Instead of
> Right-click on A1 > Paste special > Check "Values" > OK

With A1 selected,
click Edit > Paste special > check "Values" > OK

If you need to copy over the formats as well, then just do one more:

Instead of
> > Right-click on A1 > Paste special > Check "Formats" > OK

With A1 selected,
click Edit > Paste special > check "Formats" > OK

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:F3D079A3-7C83-4386-9CA1-E53CF02A2878@microsoft.com...
> Hi Max,
>
> Again, thank you for your help and I understand your comments. However,
> when I
>
> "Right-click on A1 > Paste special > Check "Values" > OK"
>
> I do not see the "normal" Paste Special options of All, Formulas, Values,
etc.
>
> I see a Paste Special window with:
>
> Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
> and "Paste link:" radio buttons and a "As:" window with
>
> Microsoft Excel Worksheet object
> Picture
> Bitmap
> etc.
>
> I am running Excel 2002 (10.6501.6735) SP3
>
> Again, thank you.
>
> BG

I am trying to do a copy "values only" of a complete worksheet into a new
workbook. However, the new copy has #REF in each cell where the original
workbook referenced the INDIRECT function. Is there any way around this?

Also, I get a #NAME in the new copy for each cell that had a used defined
function in the original worksheet.

Thanks, BG

> ... a copy "values only" of a complete worksheet

If you do an entire source sheet copy > paste special > values > ok over to
a new sheet in a new book, think you shouldn't be getting any error values
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> I am trying to do into a new
> workbook. However, the new copy has #REF in each cell where the original
> workbook referenced the INDIRECT function. Is there any way around this?
>
> Also, I get a #NAME in the new copy for each cell that had a used defined
> function in the original worksheet.
>
> Thanks, BG

Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the '(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG


"Max" wrote:

> > ... a copy "values only" of a complete worksheet
>
> If you do an entire source sheet copy > paste special > values > ok over to
> a new sheet in a new book, think you shouldn't be getting any error values
> ...
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:7EF159E0-105A-45B8-A8BA-2B2CEDC84B5D@microsoft.com...
> > I am trying to do into a new
> > workbook. However, the new copy has #REF in each cell where the original
> > workbook referenced the INDIRECT function. Is there any way around this?
> >
> > Also, I get a #NAME in the new copy for each cell that had a used defined
> > function in the original worksheet.
> >
> > Thanks, BG
>
>
>

The thoughts given earlier were specific to your orig. post's line:
> ... a copy "values only" of a complete worksheet

Are you still wanting to copy "values only" ?
The copy method which you describe below copies everything, including
formulas which is why you're facing all those errors

To copy "values only" of a complete worksheet,

Select the entire sheet (press CTRL +A)
Right-click > Copy

In a new Sheet1 in a new book,
Right-click on A1 > Paste special > Check "Values" > OK

If you need to copy over the formats as well, then just do one more:
Right-click on A1 > Paste special > Check "Formats" > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> Hi Max,
>
> Thank you for your reply. Please try the following.
>
> Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
cell
> A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
Sheet1
> and note the words 'This is a TEST!' in cell A1. Then right-click on
Sheet1
> tab and click on the "Move of Copy..." option. Set "To book:" to the
'(new
> book)' selection, check the "Create a copy" box and click OK.
>
> In the new workbook created after the OK, you should see a #REF in cell A1
> of Sheet1. This is my problem.
>
> Also, if you replace the INDIRECT with the name of a USER defined VBA
> function, you will see #NAME rather than #REF. This is my other problem.
>
> In advance, thank you for your help!
>
> BG

Hi Max,

Again, thank you for your help and I understand your comments. However,
when I

"Right-click on A1 > Paste special > Check "Values" > OK"

I do not see the "normal" Paste Special options of All, Formulas, Values, etc.

I see a Paste Special window with:

Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
and "Paste link:" radio buttons and a "As:" window with

Microsoft Excel Worksheet object
Picture
Bitmap
etc.

I am running Excel 2002 (10.6501.6735) SP3

Again, thank you.

BG

"Max" wrote:

> The thoughts given earlier were specific to your orig. post's line:
> > ... a copy "values only" of a complete worksheet
>
> Are you still wanting to copy "values only" ?
> The copy method which you describe below copies everything, including
> formulas which is why you're facing all those errors
>
> To copy "values only" of a complete worksheet,
>
> Select the entire sheet (press CTRL +A)
> Right-click > Copy
>
> In a new Sheet1 in a new book,
> Right-click on A1 > Paste special > Check "Values" > OK
>
> If you need to copy over the formats as well, then just do one more:
> Right-click on A1 > Paste special > Check "Formats" > OK
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "BG" <BG@discussions.microsoft.com> wrote in message
> news:4DF0759B-2DF8-43B7-8BD5-B9DC8AAF5A21@microsoft.com...
> > Hi Max,
> >
> > Thank you for your reply. Please try the following.
> >
> > Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
> cell
> > A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
> Sheet1
> > and note the words 'This is a TEST!' in cell A1. Then right-click on
> Sheet1
> > tab and click on the "Move of Copy..." option. Set "To book:" to the
> '(new
> > book)' selection, check the "Create a copy" box and click OK.
> >
> > In the new workbook created after the OK, you should see a #REF in cell A1
> > of Sheet1. This is my problem.
> >
> > Also, if you replace the INDIRECT with the name of a USER defined VBA
> > function, you will see #NAME rather than #REF. This is my other problem.
> >
> > In advance, thank you for your help!
> >
> > BG
>
>
>

Really don't know what's happening over there <g>, but as a last attempt,
try the alternative to right-clicking on A1 in the new sheet as per below
(my ver is Excel 97)

(Do hang around awhile here for better insights from others ..)

In a new Sheet1 in a new book,

Instead of
> Right-click on A1 > Paste special > Check "Values" > OK

With A1 selected,
click Edit > Paste special > check "Values" > OK

If you need to copy over the formats as well, then just do one more:

Instead of
> > Right-click on A1 > Paste special > Check "Formats" > OK

With A1 selected,
click Edit > Paste special > check "Formats" > OK

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"BG" <BG@discussions.microsoft.com> wrote in message
news:F3D079A3-7C83-4386-9CA1-E53CF02A2878@microsoft.com...
> Hi Max,
>
> Again, thank you for your help and I understand your comments. However,
> when I
>
> "Right-click on A1 > Paste special > Check "Values" > OK"
>
> I do not see the "normal" Paste Special options of All, Formulas, Values,
etc.
>
> I see a Paste Special window with:
>
> Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
> and "Paste link:" radio buttons and a "As:" window with
>
> Microsoft Excel Worksheet object
> Picture
> Bitmap
> etc.
>
> I am running Excel 2002 (10.6501.6735) SP3
>
> Again, thank you.
>
> BG