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:
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
BUT, when i hit Enter to accept the formula/link, the formula changes to what i
='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
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
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!!!