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?

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?

Hi,

I realise this is an old thread but it's so close to answering my question that I'll start here.

I've been given responsibility of managing and maintaining an Excel spreadsheet that make a lot of use of {=table(cell ref,)} but I don't know how this was set up; I don't know what table is being referenced, what formulae feed into the table or anything other than the address of cell ref and the value of the output.

Surely there must be a way of viewing what feeds into this function? Can anyone point me in the right direction?

Any help will be appreciated.

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.

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.

Hi ,
I tried your suggestion and got an error message saying the function was not valid.
The original cell contents is:

{=TABLE(,D29)}

and I tried:

{=INDEX(TABLE(,D29),D29)}

as my interpretation of your suggestion. But I couldn't enter it (with or without curly brackets and/or Ctrl+Shift+Enter) because the error \"That function isn't valid.\" pops up.
I see that the original formula I'm dealing with is slightly different to that you gave as an example.
If you have any suggestions then I would appreciate any help. I know you said you had only limited experience of this but I thought I'd ask.
(And I understand this is an old topic but, like I said, it seems to close to what I would ask that I thought I'd try this first)