If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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- |
#2
|
|||
|
|||
Intra-Workbook Inter-Worksheet References
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- |
#3
|
|||
|
|||
Intra-Workbook Inter-Worksheet References
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 |
#4
|
|||
|
|||
Intra-Workbook Inter-Worksheet References
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- |
#5
|
|||
|
|||
Intra-Workbook Inter-Worksheet References
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Resetting Worksheet formulas | Kevin | Worksheet Functions | 1 | May 28th, 2004 02:31 PM |
Formula cell references when sorting values in another worksheet | Frank Kabel | Worksheet Functions | 0 | March 30th, 2004 11:27 AM |
Hyperlinking a specific worksheet ina workbook | toot033 | Worksheet Functions | 1 | March 15th, 2004 06:51 PM |
removing password protection | Sikora | Worksheet Functions | 6 | October 10th, 2003 02:09 PM |
On opening a spreadsheet. | Pinda | Worksheet Functions | 7 | September 30th, 2003 09:36 PM |