computed indirect cell refererces across workbooks

[Wehrmacher]

Some time ago, with help from this group, I learned how to use named
references, with names contained in worksheet cells. (=indirect (A1) or
=indirect(NamedCell) etc.)

I now wish to use this general technicique to create a reference across
spreadsheets. For example, I have the link

='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN

This works just fine. However, I want to retrieve cells based on an
indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN
etc, essentially replacing the text 'FebruaryTCN' with something like
indirect(A1) or concatenate(A1,"TCN"). The references will be computed based
on other information in the sheet containing the computed indirect cell
references.

Again, any help will be greatly appreciated.

 

[Wehrmacher]

Hi all,

Unfortunately, this question has been greeted with deafining silence. So, I
thought I would try again with a slightly different tack, and perhaps a
different problem.

I have two spreadsheets. One spreadsheet has a number of cells that can be
referenced, while in the spreadsheet, with commands like =september sales, or
= sum(sales), or =sum(september). My problem now is to reference those same
same named cells or groups of cells, from another spreadsheet. While I can
refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs
2008.xls'!$G$9, I would like to be able to refer to them with a formula
something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales.

I have not been able to find a tutorial on how to accomplish this either
online or in either of the Excel books we have. As always, I don't think I
am the first person who would do this, and so I belive there must be a way.

Again, I would be very grateful for anyone's help on this matter.

Thanks

 

[RagDyer]

You seem to understand the naming procedures in XL, so I don't understand
your problem.

In your example, if you assign the name "September Sales" to G9, you can
access it, or make reference to it, by that name.

What am I missing in your question?

 

[Wehrmacher]

Hi,

Sorry about the long delay in replying to your post.

Let me try to be a little more clear. Let us assume that one has two
spreadsheets, workbook#1, and workbook#2. Each of these sheets have rows and
columns named. With the previous example, one of the two spreadsheeds
(workbook#1)assigns name "September Sales" to G9 because G9 resides at the
intersection of a column named September, and a row named Sales. Adding rows
or columns may move the intersection to some other cell, but within that
workbook, =September Sales will return the information desired regardless of
the actual cell (G9, H13,etc... ) So, I would like to be able to extract the
September Sales value from workbook #1 and put it into workbook #2. The
problem I have is that I can not find some function that will extract
"workbook#1!September Sales" and put it into a cell in workbook#2.

I can explicitly name G9 as September_Sales in workbook#1, then I can reach
out from workbook#2 and get workbook#1!September_Sales. However, I don't
know a way to label what could be hundreds or thousands of individual cells
in workbook#1 without going through them one at a time and using a
insert>Name>Define... process.

The plot thickens.

Suppose, I want to compute the name of the cell I want to retrieve in
workbook#1 by looking at certain row and column names in workbook#2.
Indirect references work just fine within a workbook, but I don't know how to
use a cell name computed in workbook#2 to reference a named cell in
workbook#1. For example, workbook#2 may also have a row named Sales and a
column named September, and I might want to put the September Sales value
from workbook#1 into the September Sales cell in workbook#2.

Sorry to be so long winded, but the inability to reliably link workbooks
together without having several workbooks crash every time one workbook is
altered is getting to be a real problem.

Again, thanks for your thoughts on the subject.

Best of everything

Bill

 

[RagDyer]

I would venture to say that your main problem is your *inconsistency* in WB
to WB configuration.

If you could construct a template, and then create all the WBs from that
template, what I hear you saying here, would not exist as any sort of a
problem.

If you have numerous WBs already in existence with "non-standard"
configurations, what I'm saying here is too little, too late!<g>

BTW, are you using the intersection operator within your individual WBs?

 

[Wehrmacher]

You are absolutely correct about our workbooks. They do suffer from
considerable "terra non-firma". I would guess my situation is not unusual in
business in that workbooks are maintained by various people without much
concern about the impact on others workbooks when they modify theirs.

As for the intersection operator. Put name rows in column A for example:
RowFirst, RowSecond, RowAnother.... Name Columnsin row 1 for example:
ColFirst, ColSecond, ColAnother...

A B C D
1 ColFirst ColSecond ColAnother
2 RowFirst 12 43 .016
3 RowSecond 55 12 0.25
4 RowAnother 20 8 0.86

Select the array and from the menu insert>name>create and check TopRow and
Left Column.

The formula =RowSecond ColFirst (there is a space between RowSecond and
ColFirst) will return the value at the intersection of that row column (55).
This would initially be the same as =B3. However, if someone were to add a
row between 2 and 3, then =RowSecond ColFirst would return the value at B4.

One can alternatively write the expression =indirect(A2) indirect(D1) with
tells XL to retrieve the value at RowFirst ColAnother or .016. The advantage
to using indirect addressing is that one can copy and paste that form into
large arrays that may do arithmatic based on the contents of cells in several
other arrays. I often do this beacuse in my line of work, I have costs and
quantities over months and I wish to compute average unit costs per month
etc.

Sorry, long winded again.