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