Index Multiple tables from Multiple Sheets (Same Workbook) into Master Table

Hello everyone,

I have been trying and failing to index/combine multiple tables from multiple sheets into one master table to format for uploading into the finance program. I have tried multiple formulas, mostly failing other than an IF formula that seems to work but by the time I add in all of my tables from all of my sheets, well, I figure there has to be a better option. I have looked into Power Query but all of my data is in one workbook and I had issues (probably user error) when I attempted it.

I condensed my data to 2 sheets. 100-GF (data sheet) and Visions (master table sheet). Each table is named and all of the columns are named the same throughout. Column1, Column2.....Column9. My main focus right now is pulling the account numbers from column1 of each table into the master sheet, with the master sheet having that ability to auto update in new account numbers are added into the data tables.

Thanks. Have a great day

I apologize. This is a sample from the a school budget. The data I have on there is from the general fund account beginning in 100.XXX... What I am trying to do is take all of the account numbers from the 100-general fund sheet and create a single table in the second sheet labeled VISIONS that contains all of the account numbers. But has the ability to auto update if I add new account numbers in.

The data on the VISIONS is just formulas I was trying to figure out on my own. Sorry. I fixed the VISIONS sheet to what I am trying to accomplish and reattached.

In D2

=IFERROR(INDEX('100 - General Fund'!$A$6:$A$400,AGGREGATE(15,3,((LEFT('100 - General Fund'!$A$6:$A$400,3)="100")/(LEFT('100 - General Fund'!$A$6:$A$400,3)="100")*(ROW($A$6:$A$400))-ROW($A$5)),ROWS($1:1))),"")

Copy down

JohnTopley.... Thank you so much, that actually worked more accurately than when I just copied/pasted and sorted. My actual workbook has about 25 different funds(sheets), I attached 2 more sheets, when you have time, can you show me how I incorporate more sheets into the formula?

Just when I think I am understanding the basics of what I am trying to accomplish, someone shows me there's a more efficient way. Thanks again!

I don't know how to incorporate multiple sheets in the formula.

I created tables based on the 2 new sheets and then used Power Query (a novice!) to produce the output shown in Sheet PQ.

This seems the best option to me.

I'll try Power Query again. My first attempt didn't work out great for me. Thanks again. Have a good day