Paste Links not updating automatically
 

Here are the specifics:
- working with Excel 2003
- I'm linking cells between two different workbooks
- cell in the source Workbook A contains a VBA formula to count cells of a
specific color.

Workbook A, sheet1, cell H95 paste linked into Workbook B, sheet1, cell D20
When I performed the paste link into Workbook B, sheet1, cell D20 the
references to Workbook A was an absolute reference, which I know will not
change if there are inserted or deleted rows in Workbook A. So, I did Ctrl-H
and removed the "$" to make the references relative. Everything was linked
and updating great. I even did a test by inserting rows in Workbook A and
the references in Workbook B changed accordingly. Now, I went through and
performed additional paste links in the same manner as above. Saved off the
Workbook A and B and thought I was good to go.

I then updated Workbook A by inserting rows. Opened Workbook B and clicked
on Update Links and the references did not change, so now the references are
pointing to incorrect cells. Why would the cell reference change during my
test and not after applying all my references? I've used paste links in
Excel 2000 with no problems. Is there paste link issues with Excel 2003 or
is there some obsecure condition that causes the links not to update? Any
assistance would be greatly appreciated. Pulling my hair (what's left) out,
can't seem to figure this out. Just doesn't make sense.

Thank you very much in advance to whomever can assist.

Paste Links not updating automatically
 

Steve wrote:
Why would the cell reference change during my
test and not after applying all my references?


Because you had both A and B open during your test.
The formulas in B cannot know that you have inserted rows in A while B
is closed.

I suggest you name the ranges of cells in A and reference the named
ranges in B. When you insert rows within the named ranges in A the
definition of the name changes appropriately and the formulas in B will
use the new range when you next open B.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Paste Links not updating automatically
 

Bill,

You Da Man. THANK YOU VERY MUCH!!!! I can now keep the little hair I have
left for a little while longer anyway. Have a follow-up question if you
don't mind. After getting your advice I went through and named the cells in
Workbook A and assigned (=) the names in the appropriate cells in Workbook B.
Everything worked great. Question is I'm sure this a much more efficient
way of doing what I did today.

I have 6 workbooks that feed stats to yet another workbook. The 6 workbooks
contain quite abit of information about our customers, but what I'm
interested in tracking is the status of the documents, based on specific
milestones. Here is the stats information extracted from a customer's
workbook.

A B C E F
1 Milestones: Start DR TR SR
2 Docs due: 20 10 15 25
3 Docs posted: 5 4 5 5
4 Docs in Review: 2 4 5 5
5 Docs completed: 1 4 4 4
6 Docs not received:0 2 6 6

In the stats workbook this information is stored in rows by customer.
Columns B thru F are repeated for each milestone below. From above data I
assigned a name range to each individual cell B2 thru B6, C2 thru C6, etc.
In the stats workbook I assigned the following:

B2 from above to B2 below,
then B3 above to C2 below,
then B4 above to D2 below, etc.

A B C D
E F etc
Start Start Start
Start Start etc
1 Docs Due Posted Review Completed
Not Received etc

2 Customer 1: 20 5 2 1
0

3 Customer 2:

etc.

As you can see there were 20 cells I named in each of the 6 workbooks and
then assigned each one in the stats workbook. This was a very tedious
process, but now that its done I shouldn't have to do it again. But, should
the need arise that I need to rename and/or assign the cells is there a more
efficient means of doing so. Is it possible to assign a single name to a
range (all related cells within a column, for instance B2 thru B6), then
tranpose that range into a row. Any suggestions or recommendation would be
greatly appreciated. By the way, I hope you get paid for the valuable
information you provide, thanks again.

Steve

"Bill Manville" wrote:

Steve wrote:
Why would the cell reference change during my
test and not after applying all my references?


Because you had both A and B open during your test.
The formulas in B cannot know that you have inserted rows in A while B
is closed.

I suggest you name the ranges of cells in A and reference the named
ranges in B. When you insert rows within the named ranges in A the
definition of the name changes appropriately and the formulas in B will
use the new range when you next open B.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Paste Links not updating automatically
 

Steve wrote:
Is it possible to assign a single name to a
range (all related cells within a column, for instance B2 thru B6), then
tranpose that range into a row.

Yes, if you name B2:B6 as Start then enter into the row of 5 cells, as an
array formula using Ctrl+Shift+Enter:
=TRANSPOSE(Start)



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup