I’m hoping there is a way to make my process of emailing staff much easier
I have a spreadsheet which calculates and then displays a list of staff who are out of date with training and the specific training required (result below).
I have their email address set as hyperlinks and wondered if there was a formula that will look down a column for a staff name (A column) and if so launch the hyperlink (D column) into an email reminding them along with the training required (column C)? I have the text for the email in a separate sheet.
[TABLE="width: 749"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Staff Member
[/TD]
[TD]Line Manager
[/TD]
[TD]Course
[/TD]
[TD]Email
[/TD]
[/TR]
[TR]
[TD]Bruce Amanda
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 1
[/TD]
[TD]Amanda.bruce@email
[/TD]
[/TR]
[TR]
[TD]Bruce Amanda
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 2
[/TD]
[TD]Amanda.bruce@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 3
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 4
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 1
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 5
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 6
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 7
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 8
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 2
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 9
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 3
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 10
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 11
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 7
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 8
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 12
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 10
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 11
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 7
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Buck Samantha
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 5
[/TD]
[TD]Samantha.buck@email
[/TD]
[/TR]
[TR]
[TD]Buck Samantha
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 2
[/TD]
[TD]Samantha.buck@email
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance
I have a spreadsheet which calculates and then displays a list of staff who are out of date with training and the specific training required (result below).
I have their email address set as hyperlinks and wondered if there was a formula that will look down a column for a staff name (A column) and if so launch the hyperlink (D column) into an email reminding them along with the training required (column C)? I have the text for the email in a separate sheet.
[TABLE="width: 749"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Staff Member
[/TD]
[TD]Line Manager
[/TD]
[TD]Course
[/TD]
[TD]Email
[/TD]
[/TR]
[TR]
[TD]Bruce Amanda
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 1
[/TD]
[TD]Amanda.bruce@email
[/TD]
[/TR]
[TR]
[TD]Bruce Amanda
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 2
[/TD]
[TD]Amanda.bruce@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 3
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 4
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 1
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 5
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 6
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 7
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 8
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 2
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Anderson Jemima
[/TD]
[TD]Sonia Carter
[/TD]
[TD]Course 9
[/TD]
[TD]Jemima.anderson@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 3
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 10
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 11
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 7
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Berry Grace
[/TD]
[TD]Fred Brown
[/TD]
[TD]Course 8
[/TD]
[TD]Grace.berry@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 12
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 10
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 11
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Bronski Laura
[/TD]
[TD]Maxine Trott
[/TD]
[TD]Course 7
[/TD]
[TD]Laura.bronski@email
[/TD]
[/TR]
[TR]
[TD]Buck Samantha
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 5
[/TD]
[TD]Samantha.buck@email
[/TD]
[/TR]
[TR]
[TD]Buck Samantha
[/TD]
[TD]Debbie Jackson
[/TD]
[TD]Course 2
[/TD]
[TD]Samantha.buck@email
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance