How do I create a dynamic table from another tables data?

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Thanks for your assistance. Thread title has been updated.

I've also uploaded a simplified version of what I'm trying to achieve here:
Test Sheet 1.xlsx

Basically, we have a table that has all the overview data for the companies projects
i.e.
Project name / Status / Unit

From this table I need to populate two new tables (happy to listen to other considerations).

These two tables would be handled under two different business units.
And they only care about open projects.

Business unit A, would use the master data and add in billing amounts for the upcoming months.
Business unit B, would use the master data and add in billing amounts for the upcoming Quarters.

The need would be for if the sales team signed a new project for business unit B (project 'foxtrot') it would be added to the master table, but would automatically get added to the table used by business unit B

Does this make sense?

I put your "Master Table" in an Excel Table.
Then for your "A" Unit, you could enter:

=FILTER(Table1,(Table1[Unit]="A")*(Table1[Status]="Open"))

And for your B, enter:

=FILTER(Table1,(Table1[Unit]="B")*(Table1[Status]="Open"))

This solution is only for O365 users (which you are on) because it uses dynamic array formulas.

The downside to this solution (and pretty much with any solution) is that if you remove or resort the data, the data that is added to the right of this will not stay intact. So as long as you don't re-sort, filter, or delete rows, this will work.

Originally Posted by benmartin9920

Dynamic tables in excel are the tables where when a new value is inserted to it, the table adjust its size by itself, to create a dynamic table in excel we have two different methods the once is which is creating a table of the data from the table section while another is by using the offset function, in dynamic tables the reports and pivot tables also changes as the data in the dynamic table changes.
There are two basic ways of using dynamic tables in excel – 1) Using TABLES and 2) Using OFFSET Function.
Using Tables to create Dynamic Tables in Excel
So we will first make a dynamic range.

#1 – Select the data, i.e., A1:E6.
#2 – In the Insert tab, click on Tables under the tables section.
#3 – A dialog box pops up.
As our data has headers so remember to check on the box “My Table has headers” and click ok.

#4 – Our Dynamic Range is created.
#5 – Select the data and in the Insert Tab under the excel tables section, click on pivot tables.
#6 – As we have created the table, it takes a range as Table 2. Click on OK and in the pivot tables, Drag Product in Rows and Sales in Values.
#7 – Now in Sheet where we have our table insert Another Data in 7th
Our dynamic Pivot table has automatically updated data of Product 6 in the pivot table.

I hope this information will be useful!
Ben Martin

Thansk for the help

Originally Posted by Gregb11

I put your "Master Table" in an Excel Table.
Then for your "A" Unit, you could enter:

=FILTER(Table1,(Table1[Unit]="A")*(Table1[Status]="Open"))

And for your B, enter:

=FILTER(Table1,(Table1[Unit]="B")*(Table1[Status]="Open"))

This solution is only for O365 users (which you are on) because it uses dynamic array formulas.

The downside to this solution (and pretty much with any solution) is that if you remove or resort the data, the data that is added to the right of this will not stay intact. So as long as you don't re-sort, filter, or delete rows, this will work.

Great thanks for this.
If another status was to be added:
OPEN, CLOSED, Prospect

So if I wanted to run the Filter formula to show all of Unit B AND OPEN OR Prospect - then how would I set this up?
i.e. it would return all rows that are Unit B and Open - plus Unit B and prospect
I tried the following but it does not work:
=FILTER(Table1,(Table1[Unit]="B"),OR(Table1[Status]="Open",Table1[Status]="Prospect"))

Thanks for the help

Try this:

=FILTER(Table1,(Table1[Unit]="A")*((Table1[Status]="Open")+(Table1[Status]="Prospect")))

Originally Posted by Gregb11

Try this:

=FILTER(Table1,(Table1[Unit]="A")*((Table1[Status]="Open")+(Table1[Status]="Prospect")))

You sir, are an absolute legend

Glad it worked, and thanks, but some of the people on this forum blow my mind! :-)

I trying to do something very similar. Do the headings need to match in order for it to work properly? Also where are you entering the formula? I have tried highlighting an area and just entering it in the first box but it keeps coming back with a #CALC error. I only have one filter parameter I need it to look up but would like it to return all 17 columns of data. Is that possible?