Forum Discussion
Workaround for data table that uses results from another data table.
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)
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
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.
