Intra-Workbook Inter-Worksheet References

I am using Excel 2000.

I am trying to achieve what one would think to be a very common
operation.

I have one workbook with 7 worksheets in it. In worksheet 2, I would
simply like to reference a cell in worksheet 1 using the row and
column headings of worksheet 1:

In a cell in worksheet 2:

Worksheet1!ColumnHead RowHead

Actually, the header strings actually have spaces in to separate the
the words:

Column Head Row Head

I tried using single and double quotes to make this work. No luck.
Then I noticed that even without the spaces, if there are headers in
worksheet 2 with the same labels as that in worksheet 1, Excel flips
out and completely ignores the worksheet specification behind the (!)
referencing worksheet 1 and assume you meant worksheet 2.
Furthermore, it changes whatever you wrote for worksheet 1 and
replaces it with the file name of the workbook containing both
worksheets.

???

-Chaud Lapin-

Hi
can't you split this information in several cells (and then use
INDEX/MATCH for this)?

--
Regards
Frank Kabel
Frankfurt, Germany


Le Chaud Lapin wrote:
I am using Excel 2000.

I am trying to achieve what one would think to be a very common
operation.

I have one workbook with 7 worksheets in it. In worksheet 2, I would
simply like to reference a cell in worksheet 1 using the row and
column headings of worksheet 1:

In a cell in worksheet 2:

Worksheet1!ColumnHead RowHead

Actually, the header strings actually have spaces in to separate the
the words:

Column Head Row Head

I tried using single and double quotes to make this work. No luck.
Then I noticed that even without the spaces, if there are headers in
worksheet 2 with the same labels as that in worksheet 1, Excel flips
out and completely ignores the worksheet specification behind the (!)
referencing worksheet 1 and assume you meant worksheet 2.
Furthermore, it changes whatever you wrote for worksheet 1 and
replaces it with the file name of the workbook containing both
worksheets.

???

-Chaud Lapin-

Le Chaud Lapin wrote:
I have one workbook with 7 worksheets in it. In worksheet 2, I would
simply like to reference a cell in worksheet 1 using the row and
column headings of worksheet 1:

In a cell in worksheet 2:

Worksheet1!ColumnHead RowHead


If you select the entire table on Worksheet1 (Ctrl+*) and use
Insert / Names / Create / Top, Left
then you will have range names for the rows and the columns, and you
should be able to enter the formula as

=Column_Head Row_Head with _ where there are spaces in the headings.

Then I noticed that even without the spaces, if there are headers in
worksheet 2 with the same labels as that in worksheet 1, Excel flips
out and completely ignores the worksheet specification behind the (!)
referencing worksheet 1 and assume you meant worksheet 2.
Furthermore, it changes whatever you wrote for worksheet 1 and
replaces it with the file name of the workbook containing both
worksheets.

Sounds like you may have the Tools / Options / Calculation / "Accept
labels in formulas" option set. There are a number of bugs with this
feature and most of us keep well clear of it, using defined range names
instead. If you create the range names before switching the option off
your formulas should survive intact.

Hope this helps.

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

Bill Manville wrote in message ...
If you select the entire table on Worksheet1 (Ctrl+*) and use

Tried (Ctrl+*), didn't work. Used the button in upper-left-corner to
select entire worksheet.

Sounds like you may have the Tools / Options / Calculation / "Accept
labels in formulas" option set. There are a number of bugs with this
feature and most of us keep well clear of it, using defined range names
instead. If you create the range names before switching the option off
your formulas should survive intact.

I do. I turned it on after reading the Excel Online Help about this
feature. I have to admit, when I first read it, I
thought..."Hmm...this is typicaly not something that a prudent
programmer would allow..making it so that text strings that a user
entered as heading for rows and columns might act as variable names in
the worksheet, knowing that labels might very well contain characters
that are normally not permissable in variable names, but I guess
Micrososft knows what it's doing."

My sentiments are rapidly changing.

Right after I tried your "create names" suggestion (which almost
worked), I went to look to see what was under "apply" and when i saw,
i clicked CANCEL to get out of pop dialog, and Excel crashed. This is
the 9th time it has crashed in the 5 days I have been doing it,
without my doing anything extraordinary. Is this common? I am a
software engineer, and I can assure you that I have done nothing
extraordinary on my Windows 2000 to create a hostile environment for
Excel.

Furthermore, the create names revealed a fundamental problem in what I
trying to do. In three different worksheets, I actually use the same
row and column headers. For each cell in W3, W3[][] = W2[][]*W1[][].

I assumed that Microsoft would have employed the concept of scope, so
that I could use the same names in different workshets of the same
workbook, so long as I prefixed each name in a formula with the name
of the appropriate worksheet so as to disambiguate. Apparently this
is not so - names are workbook-wide or ??.

My first experience with Access was very similar. After two days of
use, I discovered a gaping bug that apparently *everyone* had known
about for years but did not find it odd that Microsoft had not fixed
it, then it crashed every now and then which most users thought was
"normal".

I thought that, Excel, whose the docs contains references to Bessel
functions, might have been more prudently engineered.

Best,

-JC-

Le Chaud Lapin wrote:
Tried (Ctrl+*), didn't work.

Should have done - if the cursor was within the table.
Maybe you did Ctrl+8

This is the 9th time it has crashed in the 5 days I have been doing
it, without my doing anything extraordinary. Is this common?

Like I said, using labels in formulas is something we try not to do.
May not have been that causing your crashes though. More recent
versions are more robust than Excel 2000. They even try to recover
your workbook after a crashg. They also offer you the option to send
information about the crash to Microsoft, which is entered into a
database and the most frequently occurring crashes are investigated and
fixed - thus the crash rate goes down with each new release or update.

I assumed that Microsoft would have employed the concept of scope, so
that I could use the same names in different workshets of the same
workbook, so long as I prefixed each name in a formula with the name
of the appropriate worksheet so as to disambiguate. Apparently this
is not so - names are workbook-wide or ??.


By default names are workbook wide.
However, you can create sheet-level names.
e.g. Insert / Name / Define / W3!Column_Head

On the first sheet in which you use Insert / Name / Create for a given
name you will get a workbook-level name; subsequent uses will produce
sheet level names.

If you copy a sheet which has workbook-level names the copy will have
sheet-level names.

Anyway, if you do have sheet-level names in W1 and W2 then
Your formulas in W3 can be like
=W1!RowHd2 W1!ColHd2 * W2!RowHd2 W2!ColHd2

Such formulas would be somewhat hard to create and to maintain.
Most Excel users would decide to keep the layouts of W1, W2 and W3 the
same and simply have
W3!B2: =W1!B2 * W2!B2
Then you can copy this formula and paste it into the entire table in
W3.

If you knew the headings in W1 and W2 were likely to move then you
could use INDEX and MATCH functions to locate the cells to include in
the formula.


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