Trace precedents of datatable

[nicholasjcstubbs]

I have used the {=table(cell ref,)} datatable function to calculate a range of outputs from a range of inputs. Now I wish to check the calculations but trace precedents does not work with this function.  The function itself only shows the input cell and not the output cells - and it is those cells I need to trace to check that my calculation is sound.  I have drawn a blank with searching for this answer.

Any ideas?

[#2]

@nicholasjcstubbs 

In Excel, the "Trace Precedents" functionality is not directly compatible with structured table references like the {=table(cell ref,)} function. However, you can still trace the precedents of the calculated output cells in the table using an alternative approach.

One way to achieve this is by converting the structured table reference formula into regular array formulas using the INDEX function.

 Here's how you can do it:

1. Select the output cells in the table that you want to trace precedents for.
2. Go to the formula bar and edit the formula by adding the INDEX function around the structured table reference formula.

For example, if your original formula was:

{=table(A2,)}

Change it to:

=INDEX(table[A], A2)

Replace "table" with the actual name of your table, and "A" with the column header of the output you want to trace.

1. Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to enter the formula as an array formula. You should see curly braces {} around the formula, indicating that it's now an array formula.

After converting the formula to an array formula using INDEX, you should be able to use the "Trace Precedents" functionality to trace the input cells that affect the output.

Here is how to use "Trace Precedents" after converting the formula:

1. Select the cell with the array formula (the output cell).
2. Go to the "Formulas" tab in the Excel ribbon.
3. Click on "Trace Precedents" in the "Formula Auditing" group.
4. Excel will draw arrows to indicate the input cells (the structured table references) that affect the output of the array formula.

By using the INDEX function, you can transform the structured table reference into a traditional array formula, which allows Excel's "Trace Precedents" functionality to work as expected. My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

@nicholasjcstubbs 

TABLE() function from What-If Analysis is actually the macro which fills the range with calculated values. Table Precedents doesn't work with it.