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