3-d references

[C-Money]

I'm trying to place a reference of the same cell of 160
worksheets in a new sheet using a 3-d reference. However
when entering this formula: =(b:fb!q5) in the new sheet I
get a dialog box that says "file can't be found" and am
forced to explore for a file. Could someone tell me what
I'm doing wrong and/or if there's another way of
accomplishing this without having to manually type the
reference 160 times

 

[Peo Sjoblom]

Post the formula in where you use this 3D reference and what you expect to
get from it,
you can't just enter =b:bq!q5
you can enter

=SUM(b:bf!q5)



--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Harlan Grove]

I'm trying to place a reference of the same cell of 160
worksheets in a new sheet using a 3-d reference. However
when entering this formula: =(b:fb!q5) in the new sheet I
get a dialog box that says "file can't be found" and am
forced to explore for a file. Could someone tell me what
I'm doing wrong and/or if there's another way of
accomplishing this without having to manually type the
reference 160 times

First, the only place you can use 3D references in Excel is as arguments to
particular functions. That is, not even all functions accept 3D references. So
=SUM(alphamega!A1) is OK, but =ABS(alphamega!A1) and =alphamega!A1 will
both return #REF! errors.

As for the 'File not found' dialog, I can get Excel to display it using your
formula only when I don't have a worksheet named fb. Excel has a few warts, and
one of them is the ambiguity in formula syntax between external references to
single worksheet files in XL2 through XL4 .XLS format or such files in XL5 or
XL97 (and subsequent) .XLS format in which the worksheet name is the same as the
base filename (without the .XLS extension) on the one hand and 3D references on
the other. Excel doesn't require that the pathnames to files in external
references be fully qualified when entered, so "b:fb!A1" could be interpretted
as either the 3D reference to cell A1 in worksheets from b to fb or as the
equivalent of the external reference 'B:.\[fb]fb'!A1. On my system at least, if
the active workbook contains worksheets named b and fb, Excel treats =b:fb!A1 as
a 3D reference, but if either or both b or fb isn't a worksheet in the active
workbook, Excel treats =b:fb!A1 as an external reference to a file named fb
(without any extension) in the working directory of my B: drive.

So, are you sure you have a worksheet named 'fb', or could it contain a stray
space character or two?

 

[Guest]

I do have an 'fb' sheet in the file.

-----Original Message-----

I'm trying to place a reference of the same cell of 160
worksheets in a new sheet using a 3-d reference. However
when entering this formula: =(b:fb!q5) in the new sheet I
get a dialog box that says "file can't be found" and am
forced to explore for a file. Could someone tell me what
I'm doing wrong and/or if there's another way of
accomplishing this without having to manually type the
reference 160 times

First, the only place you can use 3D references in Excel is as arguments to
particular functions. That is, not even all functions accept 3D references. So
=SUM(alphamega!A1) is OK, but =ABS(alphamega!A1) and =alphamega!A1 will
both return #REF! errors.

As for the 'File not found' dialog, I can get Excel to display it using your
formula only when I don't have a worksheet named fb. Excel has a few warts, and
one of them is the ambiguity in formula syntax between external references to
single worksheet files in XL2 through XL4 .XLS format or such files in XL5 or
XL97 (and subsequent) .XLS format in which the worksheet name is the same as the
base filename (without the .XLS extension) on the one hand and 3D references on
the other. Excel doesn't require that the pathnames to files in external
references be fully qualified when entered, so "b:fb!A1" could be interpretted
as either the 3D reference to cell A1 in worksheets from b to fb or as the
equivalent of the external reference 'B:.\[fb]fb'!A1. On my system at least, if
the active workbook contains worksheets named b and fb, Excel treats =b:fb!A1 as
a 3D reference, but if either or both b or fb isn't a worksheet in the active
workbook, Excel treats =b:fb!A1 as an external reference to a file named fb
(without any extension) in the working directory of my B: drive.

So, are you sure you have a worksheet named 'fb', or could it contain a stray
space character or two?

 

[Harlan Grove]

I do have an 'fb' sheet in the file.

...

Then do you have a 'b' worksheet? If you do, then this may be something our
respective versions of Excel parse differently.

Anyway, this is distracting from the main point. You can't use bare 3D
references as operands in formulas in Excel. You can only use them as arguments
to some but not all functions.

If you need =b:fb!Q5 to refer to the values of the Q5 cells in all worksheets
between worksheets b and fb, inclusive, then you'll need to construct a list of
all those worksheet names, in order, and use an array formula like

=INDIRECT("'"&WorksheetNameList&"'!"&CELL("Address",Q5))

The reason for using the CELL call rather than just "'!Q5" is that you could
copy the range holding this array formula and paste it somewhere else, and the
reference to Q5 would adjust in the same way that copying and pasting =b:fb!Q5
would.