Copy data from master sheet in workbook to monthly sheets

[joserborges]

I need some help. My excel database has a master sheet where all data is included. It consists of 8 columns with two rows of headers (1st row: Sheet title, 2nd row: Catergories for the columns like, name, salary, emp. start date, Boro,emp. number...etc.

Help i need:

Master sheet aside, I have 12 other sheets in the same workbook (Feb-Jan), we run a fiscal year, not calender. The data that is included in the master sheet needs to copy over to the corresponding worksheet sheet month as long as the date in the emp. start date falls with the month range. For example, if my master sheet has data in rows 4,5,7,9,19,23,101...600 and the emp. start date is in the range of from June 1st, 20xx through June 31st, 20xx then all the data in that row should copy over to the next available row in the month of June's worksheet. The same should happen the months of Feb - Jan.

Feel free to ask any questions you need to clarify my post. Thank you in advance for all your help.

[Pete_UK]

I can show you how to do this using just a few formulae. Please attach a sample workbook (the FAQ describes how to).

Pete

[joserborges]

Database Main Sheet(sample copy).xlsxDatabase Main Sheet(sample copy).xlsxPete,

Thanks for the quick response

Enclosed is the enitre sheet with all of content omitted.

[Pete_UK]

Actually, it would help if you had a bit of data in there, in the style and format that you use. Just make up some data (no more than about 10 or 15 rows), with a bit of variation in it so we can see how it gets transposed to 2 or 3 of your subsidiary sheets.

Pete

[joserborges]

Database Main Sheet(sample copy).xlsxGotcha...how is this?

[nvidiaev]

This should work if you have the sheets in this order (master;Feb-Jan) any sheets after are fine just nothing inbetween.
I wrote it so that if you already have dates in a month then it will take the next free row to add the new date (this can cause duplicates if run more then once with the same data)

just change the SrartDate = 5 to what ever column number your Start Date is in.

Edit: I wrote this before seeing the sample, if you move the 2 sheets between the master sheet and Feb DOP to after Jan DOP and delete the empty rows that just have a count number in column A in every month then this should work fine

[Pete_UK]

Okay, for convenience I've inserted a new column A in the DOP_Follow_up_Log sheet (I assume this is your "master" sheet) and put this formula in A3:

=IF(E3="","-",TEXT(E3,"mmm")&"_"&SUMPRODUCT(--(LEFT(A$2:A2,3)=TEXT(E3,"mmm")))+1)

This has been copied down to the bottom of the table (the hyphens help to show this) and you can see what the formula does in the attached file - it extracts the month from the date and tags on a sequential number for each month at the end, thus helping to identify which of the monthly sheets you want the record to appear in as well as giving each record a unique identifier. You can hide column A if you want the sheet to look the same as before. I then started with the February sheet, and again inserted a new column A with this formula in A3:

=IFERROR(MATCH($A$1&ROWS($1:1),'DOP Follow Up Log'!A:A,0),"-")

Note that I also have "Feb_" in cell A1. The formula is also copied down to the bottom of the table, and identifies which row in the main sheet the corresponding record for that month appears (or returns a hyphen if there isn't one). This formula is in C3:

=IF(OR($A3="-",$A3=""),"",IF(INDEX('DOP Follow Up Log'!C:C,$A3)="","",INDEX('DOP Follow Up Log'!C:C,$A3)))

and this gets the appropriate data from column C of the master sheet. The formula is copied across to I3, with appropriate formats being set for the date and salary, and then all the formulae are copied down to the bottom of the table.

This sheet can then act as a template for all the other months - copy the sheet, rename the copy to Mar_DOP, and then put "Mar_" in cell A1, and do this for the other months - the display will automatically change. I've done this for March and April sheets - perhaps you can complete this task, then away you go.

You might also want to hide column A in each of the monthly sheets.

Hope this helps.

Pete

[joserborges]

Pete and Nvidia Thank you so much for all of your help. Can you guys point me in the direction of literature or classes that can help me develop these skills on my own or through instruction? Thank you again.

[nvidiaev]

There are probably some night school excel classes at your local highschool that can be taken to get the general idea of, and how to use formulas to get better at excel.


As for the programing aspect, I am not a good person to ask because the only classes i took were Pascal(High school) and C++(collage), after that i kind of picked up VBA from reading already writen code and asking questions. There are a lot of similarities as to how to write code between the languages. The downside to my method is that I am sure my code would be considered "sloppy" and there are probably more efficient ways to do the same thing that i do not know of, but the more i write the more i learn quicker and more efficient ways (which is the reason why i wrote that for you, to help myself ). Pascal gave me a good understanding and groundwork of programing in general and it should be the most basic of the basic (don't think its used anymore though). Only think i can say is if you don't want to jump in head first into Programing, I think learning pascal is a good stepping stone.

[Pete_UK]

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete