Formula to extract data from multiple worksheets AND auto-update when adding new worksheet

Hi,

Originally Posted by seanpod

......
I input data into a workbook at the end of each week with each worksheet representing the entire week (Monday through Sunday). I have about four weeks worth of data with each week on its own worksheet (the worksheets are the same, based off of a template I created...I just plug in the data and move on).

I would like to have a master or summary sheet that captures individual sections of data pulled from each respective worksheet and for it to be able to auto-update when I add a new worksheet (i.e., when adding a new week into my workbook)........
What is the most efficient way to do this?

. I cannot help with a Formula. Would you consider a VBA solution? I am not sure if a code could be written to kick in when you add a sheet, but a code could usually be given that would run when you wish to update a Master Sheet.

Alan

Hi seanpod,
. Sounds like the other Guys have a better idea on the best, most efficient way to do this, so their ideas Yous should consider first,. I have little experience there and cannot comment.

Originally Posted by seanpod

Hi, yes I would consider a VBA solution if one is available.

. As for VBA. If the basic idea were to use “VBA to consolidate multiple sheets to master sheet” – just try that as a search in Google or Google with Forums ( See my signature for how to do that.. for example Google search with site:ExcelForum.com consolidate multiple sheets to master sheet )
.......... you will find it is a very common requirement.
. You may be lucky and find one that does exactly what you want , but you should find one that is close.
. But for specific help here with getting a code for you, you would need to give us some test data to work on , reduced greatly in amount ( we would call this the “Before” ). But importantly we then want to see a hand filled result, which is what h should then look like after running of the macro. We would call this the “After”. We need a clear picture ( Not IMAGES ) of what you have before and what you want after with reduced test data.

. So simply provide a clear before and after. In your case I guess a couple of files would be appropriate. The Before would have a few sheets in, the master and some others. The After would, if I have understood correctly have an Extra sheet and the master would be modified by you by hand as you wish the macro to do for you.
. I would consider first the other ideas, but then is you do want to have a go at a VBA solution, then supply me that info and I will take a look sometime in the next couple of days for you.

Alan....

To summarise.

. The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...

Alan

P.s. some ways to provide that info: ( Most people use . 2a )
. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
. 2 a) To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw
. 2 b) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give..

. 3 ) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

. 4) Only as a very last resort, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Hi seanpod

Originally Posted by seanpod

....I will certainly consider searching the website for relevant information, and should I not find a suitable solution then I will consider posting here again or to you through PM......!

. Ok good luck with the searching, there is an amazing amount of info out there!!!!

. Contact me through the Thread, as then others can keep up with the Thread. I am subscribed so I should get Emails notification of the reply. However there are problems with the Forum Software and Email Notifications, so PM me as well. But do make sure you also reply in the Thread.
. If you find a code that comes close or you think could be modified to your requirements then post that too ( In Code Tags please! – See my Signature below )

Alan

P.s. When replying as you did, please chop out all but a small relevant or summarising bit of the quote, as i did with yours... otherwise the Thread is unnecessarily cluttered and difficult to read.
Thanks

Why not all data on 1 worksheet and after that use pivot table to analyze the data.

Originally Posted by oeldere

Why not all data on 1 worksheet and after that use pivot table to analyze the data.

. Sounds good ( I cannot help there either as i know nothing about Pivot Tables )

What is the most efficient way to do this?

I think all in one sheet will be the most efficient way.

I thought about that, but the amount of data is too much.

1) > 1 mln rows for the data you descipe ?

Putting all data on one worksheet and it's going to get too cluttered.