Excel: 2-variable data table

[RobBroekhuis]

I'm going crazy. I've done this many times before, and it won't work. Trying to create a 2D data table (one formula cell, row and column of input cells). I specify the row input cell and column input cell (both of which are cells which affect the value of the formula cell), click OK, and Excel gives me a real helpful "Input cell reference is not valid" error popup. The table is on a different sheet than the formulas and input cells. I've made a selection as follows before choosing Data-Table:

<formula cell>  <row input1> <row input2> ....
<column input1>
<column input2>
..
..

So what am I missing?


Rob

 

[RobBroekhuis]

OK, I figured out it works if I put the table on the same worksheet as the input cells. Seems like a very arbitrary restriction to have to have both on the same sheet - is this a documented "feature"?


Rob

 

[xlbo]

Probably similar to the Advanced Filter "feature". Not seen any documentation on it but it's not a massively used / well known area of excel.

You could try the standard workaround for Advanced filters which is using a named range instead of a cell ref. Most of these "features" where an off sheet cell ref causes an error are caused by the ! needed to reference the sheet - if you can get rid of that, it usually works.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[RobBroekhuis]

Thanks Geoff. What happened to your latin taglines?


Rob

 

[xlbo]

I used most of the funny ones up. Just got a bunch of "Chinese proverbs" so gonna work through them.

Veni, Vidi Velcro

Hope you're keeping well.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question