Hi

this is my first time interacting with an excel project which requires anything more than formulas and equations.

i have sheets labeled by month-year in my workbook added as they come in monthly. i also have a mastersheet where i have extract the relevant information for a few fields from each sheet to be presented in the mastersheet so i can easily make comparisons across months at a glance.

what i need help with is how to automate the aforementioned process
1. detecting a new sheet has been added
2.identify the relevant fields to be exported
3. export into mastersheet by adding new columns for the additional fields under a new month

i dont quite know where to begin really
thank you.

germaine

Germaine, welcome to the forum,

What version of Excel are you using and can you attach a copy of the spreadsheet even if you strip it down, as not enough information about identify fields to match to new sheet.

If you can do this, please place some comments on your master sheet of what you are trying to achieve.

Look forward to hearing from you.

Hi Trevor,

I am currently using Excel 2003 to work on this.

i've actually done up a sample sheet but i have encountered errors in uploading the file. any ideas how to tackle that problem? the error cites 'invalid file'

basically, i would need a macro that can detect a new sheet of info, and subsequently add certain columns of info from this new sheet into a master sheet, and in so doing, extend and add new headers in the master sheet as well, named after the new tab of info.

hope this was not too confusing. thank you!! please do let me know how i can get the sample mockup file i have to you so that itl be easier for you to understand. i was afraid i wasnt gonna get a reply, THANK YOU!

If you zip the workbook up then I should be able to look at the content and come up with a plan of action for you.


;)

Hi Trevor,

Thanks for the tip :). Here's the file!

Germaine Hi,

I have had a look at the spreadsheet on my way back home and have to raise the following with you:

On the Master Sheet you are showing the dates as date formats, hence any date added like May-10 is then held in format as 01/05/2010 which will not work when trying to pick up the sheet tab details to match. What I have had to do is reformat row 1 to text and also take out the extra spacings that are within the dates in row 1 cells.

There is also possibility that you don't need any VBA as there are certain types of Formula that will do what you want. What I have done is adjusted the Master sheet on a sample which I return, it has the following formula:

in Master sheet F3
=VLOOKUP(A3,INDIRECT("'"&F1&"'!A3:G13"),6,FALSE)
in Master sheet G3
=VLOOKUP(A3,INDIRECT("'"&F1&"'!A3:G13"),7,FALSE)
in master Sheet H3
=G3/F3

Now copy down the columns and across for each month required.

This solution assumes the following:
1) For each month on the Master sheet there is an equivalent Lookup sheet, ie sheets Dec-09, Jan-10, Feb-10 etc exist
2) The name of the monthly lookup sheet is in cell F1. If its in G1 or H1 change this in the formula.
3) Store name is unique, ie there is only ever one KFC in the list. If this is not so you'll have to modify the formulas but this is a start towards what you are after. Like using the store ID number.

Please find attached returned a modified workbook.

PS the reason why you couldn't upload the workbook is that Excel 2007 uses a different file extension of xlsx rather than xls.

If this doesn't give you what you are after then please feel free to reply.