• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Excel: Paste cells from 1 workbook to another, picking up local range names

Hi Experts,

If I paste a group of cells (about 100 rows x 4 columns) from one workbook to another in Excel 2003.  Some of the cells have range names (e.g. "Income") and those range names exist in both workbooks.

How can I do this copy/paste so the pasted range names access the values for those range names in the new workbook range names, rather than picking up the values for those range names in the old workbook?

For example, if the range name "Income" equals 1000 in the old workbook and 1001 in the new workbook.  When I paste the cells, which include that range name, all cells refering to the "Income" range name should show 1001 in the new workbook.

If it would be easier to copy the entire sheet from one workbook to another, in such a way as it meets the above requirements, then I'm open to that too.  I'm just not sure how to do it so that it meets my requirements.

So far my web searches re this have not been helpful.

Thanks.
tel2
0
tel2
Asked:
tel2
  • 6
  • 6
1 Solution
 
Rob HensonIT & Database AssistantCommented:
When you Copy & Paste it is only the values/formulas and formats that will be copied, not the range names.

Therefore when you paste into the second workbook, if your paste covers an existing range name then that range name will have the new values/formulas and any existing formulas that refer to the range name will use the new values.

However, if you Cut & paste that is a whole different ball game.

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Or are you copying cells that include reference to a range name from one workbook to another?

When pasting, you may get a message asking if you want to use the Range name in the new workbook, click Yes and it will use the new workbook range name.

If not, when you do the paste the formula will still show a link to the old workbook range. You can then use the Edit Links window to relink from the old workbook to the new workbook.

Thanks
Rob H
0
 
tel2Author Commented:
Thanks for the info so far, Rob.

> However, if you Cut & paste that is a whole different ball game.
I am using copy & paste.

> When pasting, you may get a message asking if you want to use the Range name in the new workbook, click Yesand it will use the new workbook range name.
Are you talking about this message?:
"A formula or sheet you want to move or copy contains the name "Income", which already exists on the destination worksheet.  Do you want to use this version of the name?
- To use the name as defined in the destination sheet, click yes.
- To rename the range referred to in the formula or worksheet, click no, and enter a new name in the Nme Confilct dialog box."

If so, yes, I have been clicking "Yes", otherwise it asks me for a new name and forces it to be different from the old name.

> Or are you copying cells that include reference to a range name from one workbook to another?
Not that I know of, but is there an easy way for me to confirm this in Excel 2003?

Thanks.
tel2
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rob HensonIT & Database AssistantCommented:
So what is the problem after you have clicked Yes?  The pasted formula will now use the range name in the destination workbook.

Thanks
Rob
0
 
Rob HensonIT & Database AssistantCommented:
In answer to:

> Or are you copying cells that include reference to a range name from one workbook to another?
Not that I know of, but is there an easy way for me to confirm this in Excel 2003?

The reason you are getting the message is because you are copying cells that include reference to a range name and the range name already exists in the destination workbook.

Thanks
Rob H
0
 
tel2Author Commented:
Hi Rob,

> So what is the problem after you have clicked Yes?  The pasted formula will now use the range name in the destination workbook.
After clicking "Yes" a few dozen times (1 for each range name), I can save the new workbook, but from then on, every time I open it, it prompts me about whether I want to "Update"/"Don't Update" links to other data sources.  Why would it do this?

> In answer to:
>>> Or are you copying cells that include reference to a range name from one workbook to another?
>> Not that I know of, but is there an easy way for me to confirm this in Excel 2003?
> The reason you are getting the message is because you are copying cells that include reference to a range name and the range name already exists in the destination workbook.

I realise that, but that doesn't answer my question above, which was "...is there an easy way for me to confirm this in Excel 2003?".  Would it be fair to say that if I open the workbooks (before copying/pasting) on a different PC, and don't get prompted to Update/Don't Update links to other data sources, then there are no external links (since the other data sources would definitely not be on the other PC)?

BTW, the 2 workbooks are my financial accounts from 2 consequitive years, so they contain the same range names.
0
 
tel2Author Commented:
Hi again Rob,

In case it isn't clear by now, to correct/clarify my original post, where I wrote:
    > "Some of the cells have range names (e.g. "Income") and those range names exist in both workbooks".
I should have written:
    "Some of the cells contain references to range names (e.g. '=Income') and those range names exist in both workbooks."

Any more thoughts on this or are you out of ideas?

Thanks.
tel2
0
 
Rob HensonIT & Database AssistantCommented:
Limited internet while on holiday this week. I will post again when back at pc
0
 
tel2Author Commented:
Hi Rob,

Hope you had a good break.

I didn't think it was fair for me to have you work further on this without providing you with a concise example to experiment with, neither did I want to give you our entire accounts spreadsheet, so I tried to reproduce it by creating a spreadsheet from scratch, and adding a range name, etc, but no joy.  So, I had a look at the real spreadsheets, and noticed 2 things, both of which resulted in the problem with the spreadsheet prompting me to "Update"/"Don't Update" links to other data sources:

1. Although the spreadsheets are basically copies of each other (2 different years of our accounts), the latter year (destination spreadsheet) had a problem with the Depn_NonTax_Profit_on_Sale range name.  I could see the range under Insert > Name > Define, but not under Edit > Go To.  I can only imagine there was some kind of corruption.  Would you agree, Rob?  So, I deleted the range name, re-added it, and I then had no problem with copying/pasting that range name.

2. There were other cells which had references like this:
    =Depreciation!S63
to other sheets in the workbook, which when pasted into the destination workbook, turned into references back to the source workbook, e.g.:
    ='[Accounts 12-13.xls]Depreciation'!S63
Any ideas why or how to prevent this behaviour, Rob?

I have manually fixed all the issues so, but I would still like to get answers to the 2 underlined questions above, please Rob.

Thanks.
tel2
0
 
Rob HensonIT & Database AssistantCommented:
Hi

Good Break Thanks!

1) I suspect the original range name was defined using a dynamic formula such as the OFFSET function rather than a fixed range. Named Ranges defined like this do not show up in the Edit > Go To list or the dropdown to the left of the formula bar. If it was dynamic, it could be that pasting into the new workbook caused an error within the formula eg invalid reference to a sheet name that didn't exist in the new workbook.

2) Unfortunately, I don't know of a way of getting round this as it is default behaviour. After you have copied and pasted though you can use the Edit Links window to change the links back to the correct workbook. Use the change source button and then browse to the correct workbook; in this case it is the workbook in which you are working so the link should disappear.

Thanks
Rob H
0
 
tel2Author Commented:
Thanks Rob,

1. I doubt it was using OFFSET, because I set up these range names, and don't know why I would have done that, especially since the whole workbook was a copy of the previous year's one, which didn't have the problem, which is why I'm suspecting corruption, but thanks for the suggestion.

2. Thanks - I've never seen Change Source, before.  In Excel 2003 I see that option is under: Edit > Links > Change Source.  Unfortunately, in this case, when I tried this just now, I got the error message "Formula is too long" for some of the cells.  A web search shows the limit might be 1024 chars, and since some formulas contain many references to cells on other sheets, they can get quite long after the workbook name has been included for each reference.  When I fixed this issue manually the other day, I think I did a Find/Replace to remove the workbook names from formulas.

tel2
0
 
tel2Author Commented:
Thanks again for your time and advice, Rob.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now