How to aggregate selected data in excel from other sheets into one?

Question

I have an excel workbook file with 10 sheets in there.

All then sheets have the exact same structure:

• Column A is Project Name

Column B is Project Version

Column C is Project Description

Column D is Component Name

Column E is Component Version

Column F is Component Description

Column F is Comments

Each sheet is filled with many entries

I would like to aggregate the following data in sheet 11:

Column A is Project Name
Column B is Project Version
Column D is Component Name
Column E is Component Version
Column F is Comments

But only if the following constraints are meet (any):

"Project Name" = "Project1" && "Project Version" = "1.2.3"
"Project Name" = "Project2" && "Project Version" = "1.2.3"
"Project Name" = "Project3" && "Project Version" = "9.1.3"
"Project Name" = "ProjectD" && "Project Version" = "12.9d"

Is there a way to perform this with formulas or differently?

Ideally to have the constrains in a separate sheet.

Answer 1

Hi Paul Edison,

Based on your description, scenario, and goals.

I suggest using Power Query as the solution to your problem.

To illustrate the solution, I created a sample with random data based on the description.

1. The file has 10 sheets with the same table structure.
2. Tables must be converted to Excel Tables.
3. Name the tables according to your needs.

   4. On the ***SETTINGS*** tab, enter the names of the *Excel Tables (Project Tabs)* you want to combine

     5.  We added the ***CONSTRAINTS*** tab as you requested

     6.  Go to the ***OUTPUT DATA*** sheet and ***"Refresh"*** the Query on the ribbon tab.

You may download this sample file here: Paul's Projects with PQ

Replace the sample data with your own, run some tests, and let me know if you need more help.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hello @Paul Edison,

Thank you for posting your question in the Microsoft Q&A forum.

Regarding your request, using VBA would be the most effective approach to achieve your requirement. However, this Excel tag primarily focuses on product features, formulas, and general functionality. As a result, we are unable to provide in‑depth assistance with scripting or VBA‑related questions here.

To receive accurate and specialized guidance, I recommend posting your question in one of the following communities:

• StackOverflow – A highly active community for VBA and macro-related inquiries. (Note: Microsoft provides this link for your convenience. These sites are not operated or controlled by Microsoft, and we cannot make any representations regarding the quality, safety, or suitability of their content. Please ensure you fully understand the risks before applying any recommendations.)

• Microsoft Tech Community – Actively monitored by Microsoft Engineers and MVPs who can offer more targeted support.

These platforms are specifically designed for technical coding questions and will provide broader visibility along with more timely responses from experts.

I apologize for redirecting you, but I want to ensure you receive the most accurate support rather than providing incomplete or incorrect guidance.

Thank you again for your understanding and patience. Although my response may not resolve your issue immediately, I am here if you have any other questions.