Generating a list that loops

[henry]

Hi All,

I am trying to create a birthday list cake roster that can be easily
updated as staff come and go. Basically I have a list of names in the
first column, their birthdate in the 2nd column. In the 3rd column I
want the name of the person whose birthday fell before the "Birthday
person" and in the 4th column I want the name of the person whose
birthday falls after the "birthday person". All nice and easy except
for the first and last persons on the list. Any ideas?

 

[Flintstone]

Hello Henry

Today’s date goes in cell A1.

A list of people’s names in cell A2, down to row how ever many people
there are, then sort ascending.

A list of the people’s birth dates in column B, obviously each date
needs to match and reflect each person in the column A.

Paste this formula in cell C2 and copy down to match the range of the B
column.

=DATE(YEAR(A$1),MONTH(B2),DAY(B2))

Paste this formula in cell D2 and copy down as before.

=IF(ISERR(DATEDIF(C2,A$1,"d")),"",IF(INT(DATEDIF(C2,A$1,"d")+(ROW()/10000))=0,"",DATEDIF(C2,A$1,"d")+(ROW()/10000)))

Paste this formula in cell E2 and copy down.

=IF(ISERR(DATEDIF(A$1,C2,"d")),"",DATEDIF(A$1,C2,"d")+(ROW()/10000))

Paste this formula in cell F2. Do not copy it any where.

=IF(ISERR(SMALL($D$2:$D$21,ROW()-1)),"",SMALL($D$2:$D$21,ROW()-1))

Past this formula in cell F3 and copy it to cell F4.

=IF(ISERR(SMALL($E$2:$E$21,ROW()-2)),"",SMALL($E$2:$E$21,ROW()-2))

Paste this formula in cell G2 and copy down to G4.

=IF(F2="","",IF(ISNUMBER(F2),MID(F2,FIND(".",F2),6)*10000,""))

Paste this formula in cell J2 and copy it to cell J4.

=IF(G2="","",IF(ISNUMBER(G2),INDIRECT("A"&G2),""))

Paste this formula to cell K2 and copy it to cell K4.

=IF(G2="","",IF(ISNUMBER(G2),INDIRECT("C"&G2),""))

Paste this formula in cell M2 and copy it to cell M4.

=DATEDIF(INDIRECT("B"&G2),A$1,"y")&" years,
"&DATEDIF(INDIRECT("B"&G2),A$1,"ym")&" months,
"&DATEDIF(INDIRECT("B"&G2),A$1,"md")&" days"

Hide columns C through G.

Matt

 

[Flintstone]

Hello Henry

Today’s date goes in cell A1.

A list of people’s names in cell A2, down to row how ever many people
there are, then sort ascending.

A list of the people’s birth dates in column B, obviously each date
needs to match and reflect each person in the column A.

Paste this formula in cell C2 and copy down to match the range of the B
column.

=DATE(YEAR(A$1),MONTH(B2),DAY(B2))

Paste this formula in cell D2 and copy down as before.

=IF(ISERR(DATEDIF(C2,A$1,"d")),"",IF(INT(DATEDIF(C2,A$1,"d")+(ROW()/10000))=0,"",DATEDIF(C2,A$1,"d")+(ROW()/10000)))

Paste this formula in cell E2 and copy down.

=IF(ISERR(DATEDIF(A$1,C2,"d")),"",DATEDIF(A$1,C2,"d")+(ROW()/10000))

Paste this formula in cell F2. Do not copy it any where.

=IF(ISERR(SMALL($D$2:$D$21,ROW()-1)),"",SMALL($D$2:$D$21,ROW()-1))

Past this formula in cell F3 and copy it to cell F4.

=IF(ISERR(SMALL($E$2:$E$21,ROW()-2)),"",SMALL($E$2:$E$21,ROW()-2))

Paste this formula in cell G2 and copy down to G4.

=IF(F2="","",IF(ISNUMBER(F2),MID(F2,FIND(".",F2),6)*10000,""))

Paste this formula in cell J2 and copy it to cell J4.

=IF(G2="","",IF(ISNUMBER(G2),INDIRECT("A"&G2),""))

Paste this formula to cell K2 and copy it to cell K4.

=IF(G2="","",IF(ISNUMBER(G2),INDIRECT("C"&G2),""))

Paste this formula in cell M2 and copy it to cell M4.

=DATEDIF(INDIRECT("B"&G2),A$1,"y")&" years,
"&DATEDIF(INDIRECT("B"&G2),A$1,"ym")&" months,
"&DATEDIF(INDIRECT("B"&G2),A$1,"md")&" days"

Hide columns C through G.

Matt