Rows for years and age based on date of birth

[Antoinelhm]

Hello all,

First time poster,

I have a list of people that have different date of birth. I made a " template " page and a vba module to automaticly create a sheet based on the template for each person in the list. Now in the template page i need to figure out how to create 2 dynamic colomns based on the date of birth of the person that tracks years and age.

So let's say someone is born in 1990 then i need
Dob : 1990/01/01

row 1 col a 1991 col b 1 years old
r2 col a 1992 col b 2 years old
r 29 col a 2018 col b 28 years.
I have to also have a way to make it future proof so that it stops at the current year +1 so that the last row as of 2018 is 2019 and in 2019 it's 2020 etc...


I can do an index match to get the date of birth from the " master " sheet but after that i need the range to vary based on the age of the person and the years. On the template page is not a problem if i get n/a but i need to have the rows created based on the info

I hope i was clear enough.

Thanks in advance for all the help !

-A

 

[Akuini]

Hello all,

First time poster,

I have a list of people that have different date of birth. I made a " template " page and a vba module to automaticly create a sheet based on the template for each person in the list. Now in the template page i need to figure out how to create 2 dynamic colomns based on the date of birth of the person that tracks years and age.

-A

Can you post your data sample in a table?
To copy a simple table from worksheet:
1. In your worksheet select the table/range then copy.
2. Back to the thread, in the reply box, scroll down and press Go Advanced
3. Paste the table
4. Click inside the table then click icon 'Table Properties'
5. Set table style to Full Grid
6. OK

A simple table would look like this:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="width: 64"]NAME[/TD]
[TD="width: 64"]ID[/TD]
[/TR]
[TR]
[TD]Mayson[/TD]
[TD]A01[/TD]
[/TR]
[TR]
[TD]Amaya[/TD]
[TD]A03[/TD]
[/TR]
[TR]
[TD]Cayden[/TD]
[TD]A04[/TD]
[/TR]
</tbody>[/TABLE]

 

[Antoinelhm]

Hello,

Here is a idea of what it would look like

SHEET "MASTER"

[TABLE="class: grid, width: 346"]
<tbody>[TR]
[TD]Name[/TD]
[TD]1st Kid[/TD]
[TD]Date of birth[/TD]
[/TR]
[TR]
[TD]Tawna Zupan[/TD]
[TD]Mira[/TD]
[TD]2014-06-01[/TD]
[/TR]
[TR]
[TD]Larissa Kromer[/TD]
[TD]Berniece[/TD]
[TD]2005-01-21[/TD]
[/TR]
[TR]
[TD]Luella Schafer[/TD]
[TD]Tad[/TD]
[TD]2001-11-30[/TD]
[/TR]
[TR]
[TD]Felecia Corson[/TD]
[TD]Carolina[/TD]
[TD]2003-02-04[/TD]
[/TR]
[TR]
[TD]Dolores Major[/TD]
[TD]Dewey[/TD]
[TD]1995-11-14[/TD]
[/TR]
[TR]
[TD]Gonzalo Dimas[/TD]
[TD]Sunday[/TD]
[TD]1994-02-02[/TD]
[/TR]
[TR]
[TD]Filomena Prosper[/TD]
[TD]Man[/TD]
[TD]1992-08-25[/TD]
[/TR]
[TR]
[TD]Ricardo Hassler[/TD]
[TD]Vallie[/TD]
[TD]1999-06-28[/TD]
[/TR]
[TR]
[TD]Gloria Lanham[/TD]
[TD]Orville[/TD]
[TD]2017-03-11[/TD]
[/TR]
[TR]
[TD]Maximo Duarte[/TD]
[TD]Leanora[/TD]
[TD]1992-07-02[/TD]
[/TR]
</tbody>[/TABLE]

That is my sample of the first page "master"

and here is a copy of the filled "template"

SHEET " TEMPLATE"
[TABLE="class: grid, width: 364"]
<tbody>[TR]
[TD]Subscriber:[/TD]
[TD]Luella Schafer[/TD]
[TD]Date of Birth:[/TD]
[TD]1995-11-30[/TD]
[/TR]
[TR]
[TD]Beneficiary :[/TD]
[TD]Tad[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Deposits[/TD]
[TD]Grant[/TD]
[TD]Withdrawal[/TD]
[/TR]
[TR]
[TD]2001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now i did the years manually but i want it to be done automatically based on the date of birth.
I'm already populating with index match the name of the kid and and date of birth and im getting the subscriber info based on the sheet name !

Thanks !

-A

 

[Akuini]

Hello,

Here is a idea of what it would look like
Now i did the years manually but i want it to be done automatically based on the date of birth.
I'm already populating with index match the name of the kid and and date of birth and im getting the subscriber info based on the sheet name !

Thanks !

-A

1. By 2 dynamic colomns you mean column Year & Deposits?

I made a " template " page and a vba module to automaticly create a sheet based on the template for each person in the list. I have a list of people that have different date of birth. I made a " template " page and a vba module to automaticly create a sheet based on the template for each person in the list. Now in the template page i need to figure out how to create 2 dynamic colomns based on the date of birth of the person that tracks years and age.

2. Do you want the macro to put 1 year, 2 year etc in column B (Deposits)?

So let's say someone is born in 1990 then i need
Dob : 1990/01/01
row 1 col a 1991 col b 1 years old
r2 col a 1992 col b 2 years old
r 29 col a 2018 col b 28 years.
I have to also have a way to make it future proof so that it stops at the current year +1 so that the last row as of 2018 is 2019 and in 2019 it's 2020 etc...

3. The template example above, can you show what the template look like before (before you change anything or add any data) & after (the result you expected)?

4. Can you show us the code?

I made a " template " page and a vba module to automaticly create a sheet based on the template for each person in the list.

5. Can you upload your sample workbook (without classified data) somewhere (maybe to dropbox.com or google drive) then post the link here?

 

[Antoinelhm]

Here is a link to the file i've removed the sensitive data & the name and birthdays are fictitious :

https://drive.google.com/file/d/1wZWJEsZXkkbT1B2s2OSXDjJ7wLkKoUKz/view?usp=sharing

Q #1 & 2 > Sorry Akuini i forgot to insert a column " Age "

so it would be like this empty

[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD="width: 84"]Year[/TD]
[TD="width: 74"]Age[/TD]
[TD="width: 108"]Deposits[/TD]
[TD="width: 101"]Grant[/TD]
[TD="width: 95"]Withdrawal[/TD]
[/TR]
</tbody>[/TABLE]

And filled

[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD="width: 84"]Year[/TD]
[TD="width: 74"]Age[/TD]
[TD="width: 108"]Deposits[/TD]
[TD="width: 101"]Grant[/TD]
[TD="width: 95"]Withdrawal
[/TD]
[/TR]
[TR]
[TD="width: 84"]1990[/TD]
[TD="width: 74"]1[/TD]
[TD="width: 108"]$2500[/TD]
[TD="width: 101"]$ 250[/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]1991[/TD]
[TD="width: 74"]2[/TD]
[TD="width: 108"]$2500[/TD]
[TD="width: 101"]$ 250[/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]...[/TD]
[TD="width: 74"][/TD]
[TD="width: 108"][/TD]
[TD="width: 101"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]2018[/TD]
[TD="width: 74"]28[/TD]
[TD="width: 108"][/TD]
[TD="width: 101"][/TD]
[TD="width: 95"]$ 3500[/TD]
[/TR]
</tbody>[/TABLE]

Now for the second part of the data I'm still not sure how i am going to get those.

For Q4 you can check in the VBA modules it's called " Sheetfromtemplate"

Thanks for looking into this !

 

[Antoinelhm]

Here is a link to the file i've removed the sensitive data & the name and birthdays are fictitious :

https://drive.google.com/file/d/1wZWJEsZXkkbT1B2s2OSXDjJ7wLkKoUKz/view?usp=sharing

Q #1 & 2 > Sorry Akuini i forgot to insert a column " Age "

Q3 so it would be like this empty

[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD="width: 84"]Year[/TD]
[TD="width: 74"]Age[/TD]
[TD="width: 108"]Deposits[/TD]
[TD="width: 101"]Grant[/TD]
[TD="width: 95"]Withdrawal[/TD]
[/TR]
</tbody>[/TABLE]

And filled

[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD="width: 84"]Year[/TD]
[TD="width: 74"]Age[/TD]
[TD="width: 108"]Deposits[/TD]
[TD="width: 101"]Grant[/TD]
[TD="width: 95"]Withdrawal[/TD]
[/TR]
[TR]
[TD="width: 84"]1990[/TD]
[TD="width: 74"]1[/TD]
[TD="width: 108"]$2500[/TD]
[TD="width: 101"]$ 250[/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]1991[/TD]
[TD="width: 74"]2[/TD]
[TD="width: 108"]$2500[/TD]
[TD="width: 101"]$ 250[/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]...[/TD]
[TD="width: 74"][/TD]
[TD="width: 108"][/TD]
[TD="width: 101"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 84"]2018[/TD]
[TD="width: 74"]28[/TD]
[TD="width: 108"][/TD]
[TD="width: 101"][/TD]
[TD="width: 95"]$ 3500[/TD]
[/TR]
</tbody>[/TABLE]

Now for the second part of the data where i insert the deposit, grants and withdrawals im gonna be inserting them from the data page. I put a few example also in the file

For Q4 you can check in the VBA modules it's called " Sheetfromtemplate"

Thanks for looking into this !

 

[Akuini]

Here is a link to the file i've removed the sensitive data & the name and birthdays are fictitious :


Thanks for looking into this !

Ok, I write a sub name "addYear".
I put this line below in your sub "SheetsFromTemplate" to call the sub.

        NmSTR = FixStringForSheetName(CStr(Nm.Text))            'use UDF to create a legal sheetname
        [COLOR=#0000cd]Call addYear(Nm)  [/COLOR]                                      '>>> calling sub addYear
        If Not Evaluate("ISREF('" & NmSTR & "'!A1)") Then       'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)           '...create it from template
            ActiveSheet.Name = NmSTR                            '...rename it
        End If

so you just hit the button 'Check for new client'.

Notes:
1. I didn't insert anything in column Deposits, Grant, & Withdrawal, because I don't know where the data is & I think you will do that part by yourself.
2. The part to add hyperlink is messed up so I disable it. We deal with this later.
3. In Template: col Year format should be General
4. In Template: why col Child #3 is col S, it's off 1 column, it should be col R.
5. In Master: why every name in col A has a trailing space?

[B][COLOR=Royalblue]Sub[/COLOR][/B] addYear(rng [B][COLOR=Royalblue]As[/COLOR][/B] Range)
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077355-rows-years-age-based-date-birth.html[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] wsM [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet, wsT [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] ce [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], rc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], x [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] dy [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], vx [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]

[B][COLOR=Royalblue]Set[/COLOR][/B] wsM = Sheets([COLOR=brown]"Master"[/COLOR])
[B][COLOR=Royalblue]Set[/COLOR][/B] wsT = Sheets([COLOR=brown]"Template"[/COLOR])

rc = rng.Row
wsT.Range([COLOR=brown]"B10:AM200"[/COLOR]).ClearContents

    [B][COLOR=Royalblue]With[/COLOR][/B] wsM
        ce = .Rows(rc).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        vx = .Range(.Cells(rc, [COLOR=brown]"E"[/COLOR]), .Cells(rc, ce))
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
 
 n = [COLOR=crimson]2[/COLOR]
[B][COLOR=Royalblue]For[/COLOR][/B] j = [COLOR=crimson]2[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(vx, [COLOR=crimson]2[/COLOR]) [B][COLOR=Royalblue]Step[/COLOR][/B] [COLOR=crimson]2[/COLOR]

    dy = [B][COLOR=Royalblue]CStr[/COLOR][/B](Year([B][COLOR=Royalblue]CDate[/COLOR][/B](vx([COLOR=crimson]1[/COLOR], j))))
    x = [B][COLOR=Royalblue]CStr[/COLOR][/B](Year([B][COLOR=Royalblue]Date[/COLOR][/B]) + [COLOR=crimson]2[/COLOR] - dy)
    [B][COLOR=Royalblue]ReDim[/COLOR][/B] va([COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] x, [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] [COLOR=crimson]2[/COLOR])
        [B][COLOR=Royalblue]With[/COLOR][/B] wsT
            [B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
                va(i, [COLOR=crimson]1[/COLOR]) = dy
                va(i, [COLOR=crimson]2[/COLOR]) = i
                dy = dy + [COLOR=crimson]1[/COLOR]
            [B][COLOR=Royalblue]Next[/COLOR][/B]
            .Cells([COLOR=crimson]10[/COLOR], n).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]2[/COLOR]) = va
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
        n = n + [COLOR=crimson]8[/COLOR]
        [B][COLOR=Royalblue]If[/COLOR][/B] n = [COLOR=crimson]18[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] n = n + [COLOR=crimson]1[/COLOR]
    
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

The workbook:

https://www.dropbox.com/s/fi65l7u09lbx6pb/a1077355a - MRexcel copy - WIP-01.xlsm?dl=0

 

[Antoinelhm]

Hey Akuini,

Thanks for that! it works would you mind explaining to me how ? like is it changing how the names are presented? what is the * for ?

but i think the code broke the index matching for the name finding because im getting a no child even thought there is one ^

1) For inserting the data i'm looking into a pivot table and getpivotdata to fetch the info. If that doesn't work then maybe a index match...

2) I'm not sure why the hyperlinking isn't working...


3) Ok that's fine

4) just a spacing mistake thing because it copy them manually. I will fix that.

5) by trailing space you name before or after... i just centered them for asthetic purposes does it change anything ? if so i can put them aligned left.

you got any idea on how to hide the template when there is no 2, 3, 4 or 5th child to avoid.
like maybe hiding those columns when the index match for birthdays returns a ''no child" value ?

-A

 

[Akuini]

Hey Akuini,
Thanks for that!

1.

Thanks for that! it works would you mind explaining to me how ? like is it changing how the names are presented?

presented where? in the result? it will be the same as the template.

what is the * for ?

what "*"?

2.

but i think the code broke the index matching for the name finding because im getting a no child even thought there is one ^

It happens in your original copy & I don't know how to fix that ( actually my knowledge about formula is very basic )

3.

I'm not sure why the hyperlinking isn't working...

Try in your original copy, say select A5 then hit the button, you'll see that A2 will get the hyperlink not A5.
But I think I can fix that later.

4.

just a spacing mistake thing because it copy them manually. I will fix that.

Ok, fix that & upload the new copy, so I can adjust my code.

5.

by trailing space you name before or after... i just centered them for asthetic purposes does it change anything ? if so i can put them aligned left.

The center aligning is fine, but you have 2 spaces in the end of all name, why?

6.

you got any idea on how to hide the template when there is no 2, 3, 4 or 5th child to avoid.
like maybe hiding those columns when the index match for birthdays returns a ''no child" value ?

I think I can do that.