Problem Linking Cells in Different Workbooks

**jeffc4442** · 01-31-2007, 12:00 PM

Hi. I have learned how to populate data in a dependent workbook/sheet from data in a source workbook/sheet. However, i'm still having problems. The dependent workbook has the same basic formatting and information as the source, but without many of the source's columns, and with smaller row height and column width.

I'm using this to track the dates when certain information passes through a system; e.g, Doc Prepared Date, Doc Issued Date, Doc Read Date, Doc Accepted Date. These would be the columns. The dependent workbook is similar, but without, say, the Doc Read Date column. The source workbok has different formatting (smaller row sizes and column sizes are the main differences).

I'm using 2 rows for each Document. Column A has account numbers. I have merged Cells A1 & A2. This information may or may not go to cells in the destination workbook that have the same reference (A1/A2). Several more columns have the cells from the 2 rows merged. Then, there is a section where i do not merge the rows...they remain separate. In the upper cell (this would be Row 1), i put a due date. In the lower cell (Row 2), i put the received date. I have several columns like this (with Row 1 & Row 2 cells unmerged). Then, later on, cells are merged again; e.g., Z1 & Z2 are merged.

I have been able to link information from the source to the destination workbook, but not every time. if i type something in, like the number 42, it works just fine. It also works if i take the source information in merged cells A1/A2 and have populated in the destination workbook merged cells A1/A2. However, whenever i try to have the source information in A1/A2 populated to D1/D2 or A3/A4 (all also merged cells). I get #VALUE! in the cell. When i mouse over the error icon by the cell, it says, "A value used in the formula is of the wrong data type."

However, the cell in the destination cell has basically the same formatting as the source cell, although the border, row size & column size might be different. They are both, for example, "General" in the drop down list on the Number tab of the Format Cells window. this is how every cell in each table is set up except for the cells with dates.

I don't have any hidden columns or any other hidden linked cells. To link a cell, i type = in the destination cell, then choose the appropriate cell in the source workbook and hit enter.

In this example, i'm linking Destination merged cells A49/A50 to Source workbook cells (merged) A1/A2. According to my books and online help, the formula is NOT created correctly:

='ITT Tracking Plan.xls'!$A$49:$A$50

I've tried unmerging the files, but i'm getting the same error. When i choose (double click) the cell in the Source workbook, the correct formula DOES show up correctly:

='[ITT Tracking Plan.xls]ITT Tracking Plan'!$A$49:$A$50

BUT, when i hit Enter to accept the formula/link, the formula changes to what i mentioned above,

='ITT Tracking Plan.xls'!$A$49:$A$50.

For some reason, when i hit enter the reference to the source workbook is no longer bracketed and the source workbook's name relaces the source workbook's sheet name.

Now, it gets weirder, i suppose. If i use the same cells...link Destination merged cells A1/A2 to the Source workbook merged cells referenced the same (A1/A2), the proper formula shows up, BUT in this situation, the formula stays the same once i hit Enter.

Surely, i'm doing something wrong in how i select the other workbook cells or something...why would the correct formula change when i hit enter?

I am using different workbooks. i do have them opened in the same Excel window...if it makes any difference i opened excel, then opened the source workbook from within excel and then the destination workbook from within excel (i.e., i didn't double click on either of the files to get excel to launch).

I'm using Windows XP Pro SP2, fully patched and updated, and Excel 2003 SP, fully patched and updated.

whew...my fingers are tired. Any ideas on how to solve this issue? THANKS!!!

jeffc4442

**Mike1001** · 01-31-2007, 01:39 PM

I'm not sure if that solves all of your problems, but I would recommend to put the formula ='ITT Tracking Plan.xls'!$A$49:$A$50 without the second linked cess, i.e. ='ITT Tracking Plan.xls'!$A$49.

Best
Mike1001

**jeffc4442** · 01-31-2007, 01:56 PM

Thanks, Mike. I'll give it a shot and let you know. Thanks for your very prompt response!

jeffc4442

**jeffc4442** · 01-31-2007, 06:03 PM

Originally Posted by Mike1001

I'm not sure if that solves all of your problems, but I would recommend to put the formula ='ITT Tracking Plan.xls'!$A$49:$A$50 without the second linked cess, i.e. ='ITT Tracking Plan.xls'!$A$49.

Best
Mike1001

Thanks, Mike...that did the trick! Pretty cool to see an 18 paragraph question answered in 2 lines! thanks, thanks, thanks!!!

**Mike1001** · 02-23-2007, 05:11 AM

Thanks for thanks, Jeff,

I want to be honest with you: 18 paragraphs are too much for me to consume!
I just browsed over your post and found this link to linked cells and thought that this looks like trouble (I have had similar issues before).

Mike

**jeffc4442** · 02-23-2007, 10:10 AM

Originally Posted by Mike1001

Thanks for thanks, Jeff,

I want to be honest with you: 18 paragraphs are too much for me to consume!
I just browsed over your post and found this link to linked cells and thought that this looks like trouble (I have had similar issues before).

Mike

Mike:

You answered the question i had about my linked cell problem.

All the rest was explanation.

You mentioned a link you found...if it was supposed to be in your most recent post, i didn't see it. could you please repost it? even though my issue is resolved, it's nice to have instructional links.

thanks again!

jeff