Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BG
 
Posts: n/a
Default INDIRECT Function impact on Copy Worksheet

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
  #2   Report Post  
Max
 
Posts: n/a
Default

... 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 <atyahoo<dotcom
----
"BG" wrote in message
...
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



  #3   Report Post  
BG
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"BG" wrote in message
...
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




  #4   Report Post  
Max
 
Posts: n/a
Default

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 mo
Right-click on A1 Paste special Check "Formats" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
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



  #5   Report Post  
BG
 
Posts: n/a
Default

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 mo
Right-click on A1 Paste special Check "Formats" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
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






  #6   Report Post  
Max
 
Posts: n/a
Default

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 mo

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 <atyahoo<dotcom
----
"BG" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum Indirect function through multiple sheets Andre Croteau Excel Discussion (Misc queries) 2 May 6th 05 10:44 AM
copy link from next worksheet mate Excel Worksheet Functions 7 March 7th 05 05:14 PM
copy a cell to another worksheet? mo Excel Worksheet Functions 1 February 26th 05 02:31 AM
how to copy 2350 hyperlink full paths to any column in a worksheet ? kontiki Excel Discussion (Misc queries) 4 December 10th 04 11:00 PM
Copy worksheet with Pivot Table and break link to original workshe setter-lover Excel Worksheet Functions 0 November 18th 04 10:29 PM


All times are GMT +1. The time now is 06:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"