Let say I have two sheets, one Database and another one NewSheet. I want to have a reference to the database so I can put some filters to the columns and be able to search for certain words or phrases in order to find a suited entry that I will then add to my main table that is down of the search table, as you can see in the next picture: I already set up a reference table like that is using some =IF() and the =Offset() functions + a scroll wheel using this tutorial ;t=658s. This is limitative for a few reasons:

1. I can't scroll through all the data since the scrollable wheel has a max range that I can't dynamically change, unless I use VBA code which I haven't tried cuz is not important right now.
2. Even If I manage to set up the scroll wheel the way I want, I still can't put filters above the searchable columns since the data is not all in the sheet at the same time.

I also tried the Special Paste > Copy Link method, it does show me the data from the Database but I can't scroll to it and also I don't know if the range can be dynamically changed as the table gets bigger and also, To be honest I haven't experimented much with this:

To mitigate this I tried making a live connection to the table from the Database sheet, similarly to how you would do it when you use "Get Data" function and "from an Access file", BUT it did not work, as when I try to use the "Get data from another table" it only lets me use a table that is in the same sheet which is very limitative:, I also tried using the function Get data from an excel file but it does not let me reference to the same Excel file.

What methods do I need to use to achieve what I want? Does anyone know another way?

And even if you can't help me I hope you have a great day!

Thank you!

Let say I have two sheets, one Database and another one NewSheet. I want to have a reference to the database so I can put some filters to the columns and be able to search for certain words or phrases in order to find a suited entry that I will then add to my main table that is down of the search table, as you can see in the next picture: I already set up a reference table like that is using some =IF() and the =Offset() functions + a scroll wheel using this tutorial https://www.youtube/watch?v=v0U1JCXm_QI&t=658s. This is limitative for a few reasons:

1. I can't scroll through all the data since the scrollable wheel has a max range that I can't dynamically change, unless I use VBA code which I haven't tried cuz is not important right now.
2. Even If I manage to set up the scroll wheel the way I want, I still can't put filters above the searchable columns since the data is not all in the sheet at the same time.

I also tried the Special Paste > Copy Link method, it does show me the data from the Database but I can't scroll to it and also I don't know if the range can be dynamically changed as the table gets bigger and also, To be honest I haven't experimented much with this:

To mitigate this I tried making a live connection to the table from the Database sheet, similarly to how you would do it when you use "Get Data" function and "from an Access file", BUT it did not work, as when I try to use the "Get data from another table" it only lets me use a table that is in the same sheet which is very limitative:, I also tried using the function Get data from an excel file but it does not let me reference to the same Excel file.

What methods do I need to use to achieve what I want? Does anyone know another way?

And even if you can't help me I hope you have a great day!

Thank you!

• excel
• excel-formula
• office365
• ms-office

• You can name a table and address the table from another sheet or even a different workbook. Start looking at the structure when you address a cell on another sheet and how it changes when it is a different workbook. Check out indirect(). – Solar Mike Commented Mar 23 at 12:55
• 1 And it looks like =FILTER() may come in handy as well – P.b Commented Mar 23 at 14:20
• Is what you refer to as 'Database' an EXCEL table (when the cell-cursor is on it, do you see the 'Table Design' menu option above the ribbon), or a simple range? – tinazmu Commented Mar 24 at 2:42
• Yes, It's an Excel table but I can also just keep it as a normal excel Sheet, it does not matter. – Noobie Commented Mar 26 at 9:08
• Isn't Data->From Table/Range what you're looking for? In PQ on the left, select Close and Load To.... In the Import Data dialog, select Table, Existing worksheet and navigate to the first cell as desired (select the sheet then the cell). – VBasic2008 Commented Mar 26 at 13:06

1 Answer 1

If the range you refer to on worksheet: Database is an EXCEL table (when active cell is inside the table a 'Table Design' menu option activates above the ribbon); then click on Table Design, and note the table name here:

You can now refer to this anywhere on the workbook as =Table1[#All], where Table1 is the name of the table. You can now browse/filter independently. It will be automatically updated as rows/columns are added/deleted.

If the range is a simple range; you can refer to it anywhere on the workbook as =Database!A1:Z99, replacing the range accordingly. You can now browse/filter independently. It will be automatically updated as rows/columns are inserted or deleted but not when appended.

The results are shown as 'arrays'; but you can treat them like normal ranges for filtering (but not for sorting).