Automatically Populate Tables from Another Table

Here a simple solution.

Create a second table for Field 1, it can be also on a different sheet.

Add the simple formula A2=Consolidated Schedule::$A2 and B2=Consolidated Schedule::$B2

Drag these formulas tenth rest of the table.

If your main table has empty cells the Filed 1 table will show 0

Create a filter for column Field

As soon as you activate the filter you see only rows for Field 1, all other rows are hidden.

Repeat this for Field 2 and Field 3.

You must ensure that all tables have the same number of rows, if not you could add new entries, but the will not be displayed.

Or you could just add a filter to your main table and set this filter based on what you need to see.

It can get difficult to maintain if you have multiple tables with identical information, even it they update automatically.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

Hello Bear34_1,

Here a version without filter.

For each "Field" you need an additional column, #F1, #F2 and #F3

Formula for F3= IF($A3="Field 1",COUNTIF($A$2:$A3,"Field 1"),"-")

It is important that you set the $ for the row, then it will always start the count in row 2 !

The IF check is optional, I thing it will make the table cleaner.

Now you ca n use XLOOKUP to get the data from the main table.

Formula for A2= XLOOKUP(ROW()−1,'Consolidated Schedule-1'::$F,'Consolidated Schedule-1'::A,"-",0,1)

ROW()-1 in cell A2 will return 1 and XLOOKUP will look in column $F for the 1 and return the information from this row in column A

It is important that you use the $ to preserve column F when you copy / drag the formula to the rest of your table.

If you like you can hide the additional columns, in the main table.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf