DT Data Table using entries in the DT as input to latter entries in the same DT

[pbanino]

I have the following interesting problem which I would like to solve within Excel without going to VBA or human intervention.


I have a data table (DT) with one variable but many outputs in each row.

The DT is very long in terms of length of columns (thousands)

each row in the DT refers to one case in a model that has many entries.


The variable used are integers 1, 2, 3 .... 1 for row 1, 2 for row 2 and so on.

The variable refers to a cell $G$8 in which the values 1, 2, 3 ... are entered as the DT is invoked.


The model uses the entry in $G$8 using VLOOKUP, to pick the row in the DT from which to enter inputs to the model. The model returns into the DT the many outputs it generated.


A new row always refers to earlier rows to pick the inputs for the model. Row 1 gets special treatment as it does not have earlier rows.


I have two questions:


1. How can I debug the sheet so that I see what entered the model and what came out from the model? I tried to have separate input columns and output columns in the DT, but they produce the same values (they should be different)


2. Is this a scheme that works in general? i.e. can earlier rows in a DT be used as inputs to later rows? If not why not and is there an alternative way?

 

[Luke M]

Sounds like a fairly good setup. The only thing I would change is getting rid of the VLOOKUPs and using INDEX instead. Since you're already using an integer value corresponding to the row you want, you could instead plug it into the INDEX like so:

=INDEX(A:A,$G$8)


Answers to questions:

1. Not really sure what the issue is. G8 contains the input...I would assume your formulas contain the output?


2. It can work. May cause excessive calculations, but that depends on the actual formulas/data that you are working with.


PS. I hope you do not mind, but I edited your post for clarity.

 

[pbanino]

Luke,


Thank you for your suggestion, I have already tried it and run into the same problem.


Everything works fine the first time I run the DT.

However once the DT has been calculated for the first time, when I change parameters (that have nothing to do with the DT) and press recalculate I am getting wrong results.


I suspect that it has to do with the way DT are executed.

This is also why I want to see what ACTUALY goes into the model and what comes out from the model when a row in the DT is executed.

 

[Luke M]

Hmm. Could you post a sample workbook possibly (http://chandoo.org/forums/topic/posting-a-sample-workbook)? I'm not sure I'm understanding how things are laid out in your DT.

 

[pbanino]

I think that I found out the source of the problem.


In a DT the calculations are done in the following order:

Top Row (the formulas row

the other rows from top to bottom.


THAN it recalculates the toprow (the formulas row).

I fail too see the logic in the order calculations are done, but this is how things have been implemented by Microsoft.


I slowed down my laptop and was able to see row by row whhen they are calculated (change values) and this is what I discovered