Auto-Populate Monthly tabs from based upon data entered on aggregate data sheet

Please note that the formula in D5 does not need to have a range associated with it. You have:

=TEXT(E5:E7,"mmmm")

though it should be:

=TEXT(E5,"mmmm")

as the TEXT function only works on one cell (the first in the range).

That being said, you could do this using a couple of formulae. As you have posted in the Macros forum, is it a macro that you are looking for specifically?

Hope this helps.

Pete

Hello Peter,

To be honest, I didn’t know how it could be done with a formula (as I’m sure you could tell, I’m self taught-badly). I appreciate your feedback on my formula in Row D; I will correct that. I would ideally like to use a formula, should I report in another forum?

I've set this up for you in the attached file.

I've used column N in all the sheets as a helper column (coloured blue). In the ALL DATA sheet I've used this formula in cell N5:

=IF(E5="","-",MONTH(E5)&"_"&COUNTIF(D$5:D5,D5))

and copied this down to the bottom of your table (the hyphens helps to show where the formula is active). This identifies the month of the birth, and gives you a unique sequential reference for each record (although you only have 3 example records, and each are in a different month, so this is not so obvious until you add more data).

In N4 of each of the monthly sheets I have used this formula:

=MONTH(DATEVALUE("1"&RIGHT(B2,LEN(B2)-FIND("-",B2))))

which returns the month number for the sheet, by looking at the heading. You could just as easily put the month number in this cell. In N5 I have used this formula:

=IFERROR(MATCH($N$4&"_"&ROWS($1:1),'ALL DATA'!$N:$N,0),"-")

which I have copied down to cell N14 in the January sheet. This tells you which row in the ALL DATA sheet that the appropriate record can be found, or returns a hyphen if no record is found. I then copied the formulae from N4:N14 into N4 of the other monthly sheets, but you might need to copy the formula down further for some of the months, as your table sizes vary.

Then you can put this formula in A5 of the January sheet:

=IF(OR($N5="",$N5="-"),"",INDEX('ALL DATA'!A:A,$N5))

and this can be copied across into B5:L5. The date of birth column (E) will need to be formatted as a date, then the formulae from A5:L5 can be copied down (to row 14 in the attached example.

I then copied that block of formulae (A5:L14) into all the other sheets, although as I said before your table sizes vary so the formulae are only present in the first 10 rows of each table - copy them down further if necessary.

If you put some dummy data in the ALL DATA sheet to test it out, you will see the effects automatically in the monthly sheets.

Hope this helps.

Pete

You are a GENIUS. This is exactly what I was looking for. I apologize I mis-posted. This is my first time on the forum, and I didn't think this operation could be done with formulas. Please accept my humble apology, and know how grateful I am to all the helpful responses.

Thank you again.

Hello Mumps,
Thank you for your response. I ran the macro, but I couldn't get it to produce the results I was looking for (this likely speaks more to my comfort level with macros than your skills with them). I believe I will use embedded formulas in the sheets. Thank you so much for your advice and contribution towards a solution.

I've been away for a couple of days, so I'm glad to hear that it worked for you.

Thanks for the rep.

Pete