How to dynamically link a spreadsheet to another

Question

I have two spreadsheets in the same workbook. Users are entering data in a table of the Sheet1 and I would like to duplicate that same table in the Sheet2 dynamically. In other words, I don't want to manually copy all the values from Sheet1 to Sheet2. On the table of the Sheet2, I would do some filtering and sorting.

I am asking because I want to keep the amount of information visible to the use as minimal as possible. If a manager look at the spreadsheet, I want him/her to have a full view with all the calculation.

Solution

1. Open the destination worksheet.
2. Click on the very first cell where you want to import the data.
3. Type = to start formula view
4. Go to the source worksheet and click on the first cell you want to import.
5. Click the check-mark icon in the formula bar or press Enter to save.
6. Go to the destination worksheet.
7. Make sure that the first imported cell is selected & hover over the lower-right corner of the cell until the mouse cursor changes to a black cross.
8. Click the cell's corner and drag the selection either horizontally (to copy within 1 row) or vertically (to copy within 1 column); make sure to mark more than data you have currently to accommodate for additions in the future.

If the data doesn't update for some reason, search Excel Help for recalculate shortcut (there are a few commands with different consequences).

Comments

[dnbrv]

Have you been able to accomplish what you wanted with my instructions?

[mtone]

Note that this will allow filtering on the destination, but not sorting. Sorting will still require manually copying these references into values. That said, maybe OP can manage to perform his calculations without resorting to sorting.