Merging information between Excel files

[k32rem]

Tim - Thanks for all your investigative help! I don't really mind having contracts for each tax rate; it's only four. I will now apply everything you've given me to the "real world" customer list and contracts. And I will let you know how it all comes together when I use it in "real time". Thanks again for all your help!

Ruth

[k32rem]

Okay, we are finally to the end of our season and I am ready to apply all you have set up for me to the real world. Everything appears to be working properly for the first 21 names in the customer database. When I got to 22 and beyond, not so much. I've attached the file so you can see what's happening (I cut the database file down to 30 names since I had to use dummy address and phone information). I deleted the entire database and copy & pasted it in again to see if that might help, but no. I also reentered the customer numbers; no go. Beyond that, I'm stuck. Can you take a look and see what the problem is? Thanks!

[tfspry]

Hello Ruth - The "Database" range needs to be redefined.
Do this .... Insert>Name>Define then click on the word Database and change the Refers to ... set the 23 to be the row number of the last row of your data.

[attachment=90615atabase name.jpg]

..
Hope that helps.

Tim

[k32rem]

Thanks so much! Worked perfectly!

[k32rem]

Okay, here I am again to ask about moving forward to another year of contracts. My first experience with doing the contracts as you set them up for me last year was great. Everything went smoothly and quickly. My question now is how to make the contract point to a different database (spreadsheet). It's all in the same workbook; I'm keeping last years customer list (2011) and will have a new one for 2012 as people begin sending their contracts back. Do I need to send you an attachment?

[tfspry]

Hello again - It is great that everything went smooth for you last year. An attachment would be useful.

Caution- If the file contains personal info on your customers, you will not want to post the file here.

I look forward to working with you.

[k32rem]

Okay, the holiday rush is over. Hope you had a great Christmas! Attached is a dummy Excel file with names and addresses. The contract tabs (Residential, Business) point to the "2011 Mailing" tab. I want them to now point to the "2012 Mailing" tab.

[tfspry]

Christmas here was very nice. I hope you had a Merry Christmas.

The "Database" range name needs to be redefined.
Try this ....
Insert>Name>Define then click on the word Database and change the Refers to ...

using your mouse to navigate…. delete the last 1 in 2011 and replace it with a 2.

Hope that helps.

[k32rem]

Yay! Works great! And so simple when you know what you're doing! Thanks again so much. Don't know what I would do without you guys! You've made my life SO much easier. Blessings to you in 2012!

[k32rem]

Okay, everything was working great after the last modification I made to my spreadsheet. I don't know what happened but I can't figure it out so I'm attaching a dummy file to look at and see what's wrong. I played around with trying to recreate what I had with 2011 and go from there, but it didn't work and now it's all messed up. I need the information in "2012 mailing" to merge to the contract sheets (Residential, Business, etc.) properly. If you have any more questions, I hope I have answers. Thanks again, and again, and again............

[tfspry]

Hello again! It seems that two different versions of Excel are being used. Has this project been converted from Excel 2003 to Excel 2010?

The issue seems to be naming(defining) of "Database" and "Cust".

Excel 2010 works a bit differently. Should be just minor adjustments.

Which Excel version is preferred?

[k32rem]

I have only been using Excel 2003; I have never had 2010 on this computer. Strange. I can't imagine how that happened! Definitely staying with 2003. Thanks!

[tfspry]

It looks like ...
1.) "Database" name has been duplicated
2.) Links to external workbook are present.

Lets correct these using the "Dummy" file sent in your most recent post...

Upon opening "Dummy" file .... choose Don't Update links to other data sources.

Go to the tab named 2012 Customer List
Click on Insert>Name>Define
Click on Database
Click Delete

Click Database again
Click Delete again
Close that box
...................

Go to the tab named 2012 Mailing
Click on Insert>Name>Define
Click on Database
Click Delete
Close that box
...................
Next is to name the Database...
On the tab named 2012 Mailing
Begin in cell A2 and Highlight all rows and columns that are needed
Once highlighted ... click the name box (it is the white box above column A)
Type in the word Database
.......
Should be all set to go

For testing on the Dummy file ...use Bill To #'s of 1 thru 9 on each of the contract sheets.

Let us know of any issues.

[k32rem]

Okay, it appears I have everything fixed per your instructions. I had to do a few reference tweaks on the 2012 Mailing sheet since I had to add a couple of columns, but that was no problem. And I had to fix the reference to the correct page on the real file, but that was an easy fix, too, from your instructions from last year. Thanks again SO much. I will try to keep things in order so this doesn't happen again next year.

[tfspry]

I am glad you have it all working again. It's a pleasure working with you. Let us know if any other issues pop up!