Extract and return a values in a summary sheet from various sheets.

Hi,

I need a formula help to extract and return a values in a summary sheet as per data contains in sheets named as 01.11.2020 & 02.11.2020 from column A to Column AH.

In a data sheets having the column A:AH in that required a data Columns values (A:B,K and AD:AH) which columns are highlighted.


Sample sheet attached with desired results.


Thanks,

Will you have the same number of rows in each of your daily sheets, and will the data be in the same order?

How many daily sheets are you likely to have in your real file?

Pete

Thanks Pete for your response,


1.) in real sheet have a around an average per sheet 110 to 130 rows (may be some time it increase or decrease) with including blank rows and sub totals.

2.)Headers are same in real sheet column starting from like C:AJ instead of showed in sample data set A:AH.

3.)daily basis updated the data with inserting a new sheet and renamed new inserted sheets that day date, till now have a almost 90 sheets.


Thanks,

I could give you a formula solution, but it would be a bit cumbersome with that number of sheets as it would have to include the INDIRECT function which is volatile.

I think you would be better with a VBA solution, which I can't help you with.

Hope this helps.

Pete

Thanks Bo_ry for your Power query solution.

in a original file having a merged cells in a headers and other top rows, how can i get solution in power query with merged cells,

however that's the client format i cannot modify and edit it.

sample modified sheet attached for your reference.

thanks again,

Many thanks Bo ry for your amazing solution.