Workaround for data table that uses results from another data table.

[CamperVan365]

I set up a data table that depends on optimization results from another data table.  Evaluation of the 2nd data table is erratic - some entries are correct, but not all.  

Is there a way to set it up so that each call from the 2nd data table triggers full re-evaluation of the 1st data table?

Is there a suggested work around using a VBA macro to build the 2nd data table?

Why doesn't Microsoft fix this problem so that users won't have to go through the experience of getting erratic results and not understanding why?

( I am using Microsoft Excel for Mac version 16.68. )

[#2]

@CamperVan365 

There are data tables and data tables and data tables. All kinds of data can be entered into a table.

You could help us help you if you were more complete in your descriptions of the kinds of data, and perhaps, as well, how it is that your table 1 differs from your table 2, how each is created in the first place, what the relationship is ("relationships are"?) between the two. How are they used? What is the nature of a "call from the 2nd" that "triggers" some sort of re-evaluation of the first (and why that direction, since the second is the less reliable?

You see the point? What you've described is no doubt clear in your own mind, but you are close to it and making assumptions about those of us reading your description. Is it possible--without violating any confidentiality--for you to post a copy of the workbook in question on OneDrive or GoogleDrive, pasting a link here that grants edit access? That too would help us help you.

(I'm quite sure it's not related, but I'm also on a Mac and my version is 16.69)

[#3]

The "Data Tables" were each created by using the "Data" menu and selecting "Table", at which point a "Data Table" window popped up asking for either a "Row Input Cell" or a "Column Input Cell".  

Both of the Data Tables I was referring to happened to use Column Input Cells.

Each Data Table serves the usual role of taking a list of input values from the left column, sequentially sending these values to a "Column Input Cell" located somewhere else on the spreadsheet, and then recording how the value of another cell in the header of the table changes in response to the changes in the Column Input Cell.

Due to the way that Excel is written, any time the data recorded in a "Data Table" is directly or indirectly dependent on results from another "Data Table" the evaluation procedure is unreliable.

For example, see this reference:  https://excelhelphq.com/why-your-excel-data-table-does-not-work-and-how-you-can-fix/

@mathetes 

[#4]

Thanks for the education. I've never seen that type of data table; all of the ones I've worked with contain basically static data. That static data may get there (I do have some of these among my own workbooks) by means of FILTER or VLOOKUP or XLOOKuP or the like, but I don't experience the kind of instability and unreliability you are.

[#5]

how about show some raw data in detail and expected result？
all roads to Roman。
if data table not easy to accomplish one task，maybe another way available。

[#6]

Here is the solution that I came up with for my own question.
Perhaps this routine will help someone else with the same problem.

' this VBA routine effectively automates building a data table, and works even if the data table depends on other data table results
' the corner cell is used to pass the independent variable to the rest of the worksheet
' the rest of the top row is used to get the dependent variables ( results ) from the rest of the worksheet
' the rest of the left column contains the list of values to insert sequentially into the corner cell
' the routine sequentially puts each left column entry into the corner cell
' and copies the results from the top row into the row next to the left column entry

' for my application, the corner cell was located at row 7, column 9
' the rest of column 9 ( from row 8 down to row 70 ) contained the list of values to put into the corner cell
' the rest of row 7 ( from column 10 to column 12 ) contained references to get the results I wished to tabulate from the rest of the worksheet


Sub EvaluateDataTable()
cornerRow = 7
cornerColumn = 9
endRow = 70
endColumn = 12
Set cornerCell = Worksheets("Sheet1").Cells(cornerRow, cornerColumn)
For rowCounter = (cornerRow + 1) To endRow
cornerCell.Value = Worksheets("Sheet1").Cells(rowCounter, cornerColumn).Value
For columnCounter = (cornerColumn + 1) To endColumn
Worksheets("Sheet1").Cells(rowCounter, columnCounter).Value = Worksheets("Sheet1").Cells(cornerRow, columnCounter).Value
Next columnCounter
Next rowCounter
End Sub

[#7]

@CamperVan365 then the question is: can you use data from a data table as an independent variable in this macro? Otherwise, you can use Data Table instead of Macro to automatize process and yet get the same result.