Data from specific tabs on multiple workbooks

[tony mcpike]

I have a number of workbooks named A, B, C, D, and E for 5 different areas of the business.
each workbook has 52 tabs (one for each week of the year)

I would like to create a worksheet that if I type e.g. 1 , it will go to all 5 workbooks and automatically transfer in the relevant I data from tab 1 of each workbook so that I can see on one page.

 

[Joe4]

Welcome to the Board!

I would highly recommend going a different route.
That is going to get REALLY messy and be extremely cumbersome to manage, and I suspect performance is going to take a massive hit when dealing with workbooks of that size.

What you really are describing is a RELATIONAL DATABASE. Excel is a spreadsheet program, NOT a Relational Database program. While you can often time "brute force" Excel to work like a Relational Database program, it is often clunky, inefficient, and difficult to work with (since that is not what it was designed to do). You would be much better off storing your data in a relational database program like Microsoft Access, SQL, MYSQL, or Oracle. They were designed for exactly this type of thing, and handle it much more elegantly than Excel.

Once all the data is in the database, as long as you have designed the tables according to the rules of normalization, you should be able to get the data you want dynamically with simple queries.

 

[tony mcpike]

Thanks for your response. I guess that you are probably correct. Unfortunately I don’t have access to any relational databases or experience working with them.
I could try my organizations IT department to see if access to a database could be made available …. But not sure that it will be.

 

[Joe4]

Yeah, it will be very cumbersome to work across multiple large files, as opposed to having all the data in the same file/database.

You may want to take a look here. There are some free options available:

20 Best Free Database Software in 2024 | Technostacks

Find out here the list of best open-source and free database software that will help you to choose the perfect database for your project requirements.

technostacks.com

Even if you just use one of these programs to store all the data, you could then use Power Query in Excel to perform database-type functions on it.

 

[tony mcpike]

Yeah, it will be very cumbersome to work across multiple large files, as opposed to having all the data in the same file/database.

You may want to take a look here. There are some free options available:

20 Best Free Database Software in 2024 | Technostacks

Find out here the list of best open-source and free database software that will help you to choose the perfect database for your project requirements.

technostacks.com

Even if you just use one of these programs to store all the data, you could then use Power Query in Excel to perform database-type functions on it.

Thanks again for the advice