Retrieving data using label intersections across workbooks?

Lets say that you have some data in B and C. And you entered appropriate labels in B1 and C1.

Select the range in B including B1, activate Insert|Name|Create, and choose Top Row.

The labels that you have in A from A2 on (row labels) must be treated as above. You have to choose Left Column. Once you created labels, you will be able to use the intersection operator.

BTW, there are also alternative ways to achieve the same thing.

Aladin

========

The syntax is...

=Label1 Label2

That is, a single space between the column and row labels.

==========

Re: The syntax is...

Aladin,

Thanks for the reply. I can't seem to make it work. Here's more details.
I have two separate workbooks, OverallStats.xls and TradeStats.xls. In OverallStats.xls the worksheet names is Trades and I have columns and rows like:

A B C
1 Total Total
2 trades trade days
3
4 3/23/01 2 1
5 3/30/01 11 6

If I specify: =trades 3/30/01
in a cell in OverallStats.xls it will return 11 as expected. Now, I want to do the same in the other workbook.
But, If I specify: =[OverallStats.xls]Trades!trades 3/30/01
in a cell in a separate workbook, in this case TradeStats.xls, I get an error.

I have tried:
=[OverallStats.xls]Trades!trades `3/30/01`
=([OverallStats.xls]Trades!trades 3/30/01)
=([OverallStats.xls]Trades!trades `3/30/01`)
and others but no luck.

Defining names or label ranges in OverallStats.xls doesn't seem to help.

Anyway, the idea is to be able to reference an intersection in OverStats without having to even look at where the intersection actually is like:
=B12/[OverallStats.xls]Trades!trades 3/30/01

If you can see from the above how I could make this work or another approach altogether, I would appreciate your assistance.

Thanks again,
AL

should look more like:

I have two separate workbooks, OverallStats.xls and TradeStats.xls. In OverallStats.xls the worksheet names is Trades and I have columns and rows like:

Uh, except it looks more like this :-)
___ __________ ________ ____________
|___|____A_____|____B___|____C_______|
|_1_|__________|_Total__|__Total_____|
|_2_|__________|_trades_|_Trade Days_|
|_3_|__________|________|____________|
|_4_|__3/23/01_|____2___|_____1______|
|_5_|__3/30/01_|___11___|_____6______|

Re: should look more like:

Well, I guess I could try all night to make that
look right but without a fixed width font display
I won't have much luck. I hope you can tell what I
was trying to show.

Re: should look more like:

AL,

I now uderstand that you want to use the labels that you created in the OveralStats workbook in the TradeStats workbook in formulas that make use of the intersection operator.

I've looked into this: it seems you can't have access in a workbook to labels that exist in a different workbook. I believe, even if we are wrong on this count, it wouldn't be hassle-free.

I've a different proposal which, I hope, would be easier to apply.

I'll use you sample data

{"","Total","Total";"","trades","Trade Days";35511,2,1;35518,11,6} [ The big numbers are serialized dates, so don't worry about them. ]

which occupy the range A1:C4 in OverallStats.xls.

Select the data range A3:C4 (thus exluding the labels) and name it OSTATS (from overall statistics) via the Name Box (see the formula bar).
Select the labels range A2:C2 and name it FIELDS via the Name Box.

Open TradeStats.xls.
Activate the option Insert|Name|Define.
Enter OSTATS as name in the Names in the Workbook.
Enter the following formula in the Refers To box:

='OverallStats.xls'!OSTATS

Activate Add.
Enter FIELDS as name in the Names in the Workbook.
Enter the following formula in the Refers To box:

='OverallStats.xls'!FIELDS

Activate OK.

Activate the worksheet where you need to do computations.

For example,

in A4 enter: 30-03-2001 [ the date you are interested in ]
in B4 enter: =VLOOKUP(A4,OSTATS,MATCH("trades",FIELDS,0),0)

Alternatively, define none of the above within TradeStats.xls and just use:

=VLOOKUP("30-03-01"+0,'OverallStats.xls'!OSTATS,MATCH("trades",'OverallStats.xls'!FIELDS,0),0)

Aladin

Thanks for the great reply

Aladin, thnaks for the detailed reply. I will have to try this later on tonight.

thanks again,
AL

AL, I now uderstand that you want to use the labels that you created in the OveralStats workbook in the TradeStats workbook in formulas that make use of the intersection operator. I've looked into this: it seems you can't have access in a workbook to labels that exist in a different workbook. I believe, even if we are wrong on this count, it wouldn't be hassle-free. I've a different proposal which, I hope, would be easier to apply. Aladin