0

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: enter image description here 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.com/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: enter image description here

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:enter image description here, 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!

Improve this question
5
  • 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(). Commented Mar 23 at 12:55
  • 1
    And it looks like =FILTER() may come in handy as well 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? 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. 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). Commented Mar 26 at 13:06

1 Answer 1

Reset to default
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:

(table) Properties group

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.