Forum Discussion

holly joiner's avatar
holly joiner
Copper Contributor
Mar 24, 2018

Need major help!

Hi everyone.  I've hit a wall and need some help!  I'm trying to put together a workbook where the first page is registration info and following sheets are score sheets.  Once a child is registered, ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 25, 2018

Okay, another attempt.

 

I make some correction in your formulas.

Since ages (column E) starts from zero and blank cell in formulas also taken as zero, we add empty text to such cells if no data registration

=IF(ISBLANK(D3),"",INT(('Data Values'!$B$31-D3)/365))

Similar logic for Age Group (column E) plus initial formula was simplified:

=IF(ISBLANK(D3),"",VLOOKUP(E3+(F3="Yes"),'Data Values'!$A$4:$B$29,2,FALSE))

With that numbers in column B are generated as

=IF(E3="","",COUNTIF(E$3:$E$1111, $E3)-COUNTIF(E$3:$E3, $E3)+1+COUNTIF(E$3:$E$1111, "<"&$E3))

and attached

holly joiner's avatar
holly joiner
Copper Contributor
Mar 25, 2018

WOW!!!  Awesome job!!  I'm not sure i completely understand it, but it seems to work. I'm going to have to study this so that I can use it again!  Thank you so much!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 26, 2018

    Assume you have only one group. You calculate how many records were registered for that group totally and deduct number number of records registered before. Plus 1 - that gives you the number to assign.

     

    If you have few groups to above number you add total number of records registered for all groups with numbers below the current one.

     

    Assigned numbers are recalculated with each record added/deleted if your Excel in automatic calculation mode (which is default behavior). 

Resources