How to link Excel data table to input cells on multiple or different worksheets?

Strategy

The strategy is to have your original excel sheet’s input cells to change only when the data table is “active”. The strategy takes advantage of how a data table works. A data table works by changing the value of its input cells based on each row / column heading combination, and copying over the result onto the data table cell.

So when a data table is calculating, it modifies the value of an input cell(s). Any cell from another sheet can link to the input cell. When the data table is calculating, it will trigger all other cells from other sheets that link to the input cells.

Basically, other cells can have a “default” value, and switch to the data table value when the data table is calculating.

Step by Step Solution

1. Complete your original spreadsheet without any data tables. Let’s call this sheet “Sheet1”.
2. Add a new worksheet or move to an existing worksheet dedicated to the data tables. Let’s call this sheet “Sheet2”.
3. Set up the data table like normally. Link the row and/or column input(s) to a cell(s) in the same worksheet. Let’s say row input = B1, column input = B2.
4. If you have multiple data tables, repeat Step 3. If the inputs are the same, link to the same cell(s) as the previous data table you just step up.
5. Change B1 and B2 formula to =””. The cells should look “empty” or blank.
6. Now is the time to link the two sheets together. Go back to Sheet1.
7. On Sheet1, find the cells that represent the input variable(s) that the data table is using.
   Edit the input cell representing the row input:

   =if(Sheet2!B2="",[original value],Sheet2!B2)

   Edit the input cell representing the column input:

   =if(Sheet2!B3="",[original value],Sheet2!B3)

   Replace [original value] with the original values of the cells.

8. Go back to Sheet2, your data table should work properly.