Array Formula to collate data from multiple worksheets to a master sheet

[jaredli]

Hello Experts

Can you help me to get this problem sorted? I have few worksheets under the same heading with different data set across the board. All I need is my Master Worksheet to show all data being collated from all worksheets. As soon as anyone updates any row in any particular worksheet, it would automatically update the result in the Master Sheet. It could either be vb or formula but I would prefer having the array formula to do at ease.

This would be basically a training register where all Managers would be updating their own dept's data and the management would see the complete picture of the Organisation. I'v attached the sample file here. Please

[JohnTopley]

See attached as one approach:

[MarvinP]

Hi jaredli,

Because you have Excel 2016, you have the new tool called Get&Transform available. I've used that new tool to append all sheets into the master. I created 3 Dynamic Named Ranges of your department sheets using the Names Manager. This will allow these tables to grow to any number of rows.

Then using Power Query (Get & Transform) I created a "Connection Only" to all three sheets. Lastly I created an Append query of the three tables and put the answer on the Master Sheet. See the attached. NO Formulas needed. You simply need to refresh the connection after data is added or removed and it will update.

PQ Append Master from DNR sheets.xlsx

See https://www.powerquery.training/port...ta-from-files/ if you want each department to keep their own workbook and you only need a master with a single sheet. Time to learn Power Query?

[oeldere]

or with the macro below,

See the sheet consolidated in the attached file.

Please Login or Register  to view this content.

[jaredli]

Thanks MarvinP. This would be participated by various people and they might not have 2016 version of Excel. Can it be done in array formula?

[jaredli]

John, is there any easy way out? it would be complex when i'd have hundreds of dataset with 15 department information.

[jaredli]

Thanks Oeldere. This vb code would be pretty tough to maintain when you change the headers and ranges frequently according to the need. everytime you have to change the code as per the changes. is there any other way?

[oeldere]

Thanks Oeldere. This vb code would be pretty tough to maintain when you change the headers and ranges frequently according to the need. everytime you have to change the code as per the changes. is there any other way?

Reading your comment I get the feeling you did not tried the code.

Did you?

[jaredli]

Reading your comment I get the feeling you did not tried the code.

Did you?

I tried for sure. I submitted a sample workbook and the actual workbook is having a massive information store with the nature of regular changes in the training headings.

[MarvinP]

Hi jaredli,

Only the Master needs to have 2016 installed for my solution. The other workbooks do not need a newer version of excel.

And.. CSE formulas are much harder than my PQ solution and I'm a lazy guy. I keep looking for the solution that has no formulas or VBA to accomplish an answer. Sorry, I don't want to work on a harder solution than the one I've provided.

[JohnTopley]

i'd have hundreds of dataset with 15 department information.

... not sure what you mean by dataset but I suspect this NOT something you will do easily and productively with formulae.

As I am only on Excel 2010, I cannot comment on Marvin's solution but I would expect the "built-in" features he advocates would be pretty efficient.

regular changes in the training headings.

If I interpret this correctly, it also mitigates against ANY easy solution whatever technique is used.

You vastly over simplified the nature of your requirement so you naturally get responses based on what you have posted.

I suggest you post a much more detailed requirement statement.