Our original error message stems from the input cell being on a different sheet. What if we could have an input cell on the Calculation Sheet AND the Output Sheet at the same time? With some simple IF logic, we can do just that. It is prone to unintentional deletion (you can’t protect what you can’t see) and it makes a model challenging to review.ĭue to these downsides, the Two Linked Tables approach is less than optimal.
![excel vba on table new row event excel vba on table new row event](https://i.stack.imgur.com/9SpMM.png)
This approach tempts model builders to hide the “live table,” either by moving it well off to the right side of the model where nobody can see it or by changing all the text white and deleting all borders. This practice is dangerous.To solve this, we would need to delete the data in the “live table”, add the row, recreate the data table and re-link the “dummy table” on the Output Sheet. This is inefficient and prone to error or omission.
![excel vba on table new row event excel vba on table new row event](https://www.codegrepper.com/codeimages/excel-vba-find-get-last-row-in-column.png)
we want to add a new row between EBITDA and Margin in our example)? This would cause an error (“Can’t change part of a data table”). What if we wanted to add another line or column on the Calculation Sheet in a spot that would affect the “live table” (i.e.And since we’ve linked the titles and growth rates and margins (always do this!) the tables should match. Leaving the “live table” embedded within the Calculation Sheet does come with downsides, though: This approach works visually and creates a centralized location with key outputs. Rather than rebuilding the data table on the new Output Sheet tab, we can simply create a “dummy” table that links back to the “live” table on the Calculation Sheet. In the example below, we can see that all of the cells in the table (including the row and column headings) are linked back to the “live” data table on the Calculation Sheet. But my client really wants a separate Output Sheet what is an analyst to do? In this instance, Excel is telling us that we cannot have the input cell references on a different sheet from the Data Table. But when you click OK, you will get an error message stating “Input cell reference not valid”: a classic, unclear Excel error message. Our first instinct is to setup the Data Table on the Output Sheet and then link back to the Calculation Sheet for our Row Input and Column Input cells in the dialog box.Įxcel will allow you to enter references to the Calculation sheet in the dialog box. Our example included a simple calculation of 2019 EBITDA and a Data Table to assess what the resulting EBITDA would be under a range of revenue growth and EBITDA margin assumptions. We connected the data table and obtained results that worked as expected.īut a common issue when building more complex models is the desire to create a separate Outputs Sheet that includes a number of key summary tables or charts in one place, rather than spread throughout a model. In the previous example, the Data Table we created belongs on an Output Sheet rather than in the body of the model. This second article will dive into more detail on one of the particular issues we identified, which is that Excel’s data table must be on the same worksheet as the original inputs being sensitized.
#Excel vba on table new row event how to#
That article described the steps to create a Data Table and how to troubleshoot some of the most common problems users experience. In last month’s BenchMarq we explained Data Tables, one of Excel’s most powerful analysis tools. Data Tables Part II – Building an Excel Data Table Across Worksheets