Data tables

[maurits]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Some questions regarding data tables (the functionality available under Data->Table...):

- I can't find any documentation in the Excel help. Am I just missing it, or is this functionality not documented at all? (a related question: does the Excel help contain a list of all menu entries together with relevant documentation?)

- The row/column input cell must apparently be on the same sheet as the data table, otherwise the arcane error message "Input cell reference is not valid." appears, which is highly confusing. I can't see any deeper conceptual problems that would keep this functionality to work across sheets - after all, I can work around the limitation by referencing a cell on the same sheet from the other sheet and then using the former as input cell.

- After I inserted a data table into a very complex model for sensitivity analysis, Excel basically came to a standstill whenever I entered data elsewhere, even if it wouldn't affect the data in the table at all. I had to remove the tables to make working in the file possible again. Is this normal behavior?

Consider this to be an appeal to Redmond to (a) lift that seemingly arbitrary limitation or at least (b) to come up with a more descriptive error message like "Because of design limitations, you cannot use cells on other sheets as input cells."

-maurits

 

[Mike Middleton]

maurits -

You seem to have an excellent understanding of the features and limitations
of the Data Table feature.

I also cannot find information about Data Tables in Mac Excel 2008 Help.

I think that all versions of Windows Excel and Mac Excel require the
row/column input cell to be on the same sheet as the data table.

In case there is a volatile function related to your data table, perhaps
unbeknownst to you, while editing the worksheet you can avoid recalculation
by choosing Excel | Preferences | (Formulas and Lists) Calculation |
(Calculate Sheets) Automatically except for data tables.

This newsgroup is primarily peer-to-peer user oriented, so if you want to
make "an appeal to Redmond," you could choose Excel's Help | Send Feedback
about Excel.

- Mike
http://www.MikeMiddleton.com


Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel
Some questions regarding data tables (the functionality available under
Data->Table...):

- I can't find any documentation in the Excel help. Am I just missing it, or
is this functionality not documented at all? (a related question: does the
Excel help contain a list of all menu entries together with relevant
documentation?)

- The row/column input cell must apparently be on the same sheet as the data
table, otherwise the arcane error message "Input cell reference is not
valid." appears, which is highly confusing. I can't see any deeper
conceptual problems that would keep this functionality to work across
sheets - after all, I can work around the limitation by referencing a cell
on the same sheet from the other sheet and then using the former as input
cell.

- After I inserted a data table into a very complex model for sensitivity
analysis, Excel basically came to a standstill whenever I entered data
elsewhere, even if it wouldn't affect the data in the table at all. I had to
remove the tables to make working in the file possible again. Is this normal
behavior?

Consider this to be an appeal to Redmond to (a) lift that seemingly
arbitrary limitation or at least (b) to come up with a more descriptive
error message like "Because of design limitations, you cannot use cells on
other sheets as input cells."

-maurits

 

[CyberTaz]

I don't know that it will address your concerns but the Help for Office 2003
usually cross-references to Excel 2008 rather directly. Excel 2008 Help is a
known disaster area. Try here:

http://office.microsoft.com/en-us/excel/FX100646961033.aspx

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[maurits]

Mike & Bob,

thanks for both your excellent suggestions.

In case there is a volatile function related to your data table, perhaps
> unbeknownst to you, while editing the worksheet you can avoid recalculation
> by choosing Excel | Preferences | (Formulas and Lists) Calculation |
> (Calculate Sheets) Automatically except for data tables.

great! That's at least a viable workaround.

This newsgroup is primarily peer-to-peer user oriented, so if you want to
> make "an appeal to Redmond," you could choose Excel's Help | Send Feedback
> about Excel.

hmm; a while ago I sent a request for help to MacBU member Nadyne Richmond afer I got totally confused about the different channels for bug reporting and user feedback that Microsoft offers (or does not offer). My request prompted this semi-official statement: http://blogs.msdn.com/nadyne/archive/2009/12/03/q-a-how-do-i-submit-bugs-for-office-mac.aspx . So my understanding is that the feedback page is for feature requests, not for bug reports. Also, my understanding is that the best option for reporting bugs is posting in this forum, in the hope that some MVP picks up the issue and reports it back to Redmond (or Mountain View, in this case).

I don't know that it will address your concerns but the Help for Office 2003
> usually cross-references to Excel 2008 rather directly. Excel 2008 Help is a
> known disaster area. Try here:

Again, good advice. I ended up googling, and didn't have too much of a hard time finding some documentation, including Microsoft's Office for Windows help. In fact, the best description I found was in Mike's book chapter on treeplan.com ;-) Thanks! Next time I should probably skip the disaster area and head to Google directly.

Thanks again,

maurits