Linking data from a table into a worksheet

Occasional Visitor

Hi! I want to link data (not cells) that I have in a table to another worksheet. Making a reference to a cell in the source table does not work because if I manipulate the table by using filters the destination worksheet keeps pointing to the same original cell that is displaying a different value now.


I have a table with oil output values from different fields and for different years. I need to work with a particular oil field in a worksheet using output values of that particular field. So, I want to link my worksheet to the database table rather than duplicate the data by copying and pasting from the table to the worksheet.

2 Replies
You can use Get and transform and use only connection to the data instead of copying and pasting.
If you have values filtered, those cells which are being filtered out (not visible) are still there, and references will still point there and be completely valid. If you're wanting to return only the visible cells, that is a whole other ball of wax. It can certainly be done, but the formula structuring is a little complex.

There are various ways to accomplish what you're trying to do. It really all depends on what data you have, where it's located, and how it's structured.

When you say "database", what kind of database is it? SQL Server? MySQL? PosgreSQL? AS400? Or do you mean another Excel workbook?

When you say "I need to work with ... output values of that particular field" what does that mean exactly? Do you want to aggregate that data? Use it for reference only? Plot charts to it?

When you say "I want to link my worksheet to the database table" what does that mean? Almost all data from a database (assuming you mean a proper database, not an Excel workbook) should be a copy of the data. It's generally dangerous and dissuaded to work with actual master data. Having some layer protects your data.

Please be entirely specific in where your data is and what you need done with it. Chances are we can recommend a solution which is best.