I have an Excel spreadsheet which contains several columns
Column A contains sequential dates as in a calendar
Column B contains the one, two or (occasionally) three names of volunteers that manned the Visitor centre that morning
Column C contains the one, two or (occasionally) three names of volunteers that manned the Visitor centre that afternoon.
There are about 50 volunteers that will appear in columns B and C, some more often than others. The names of the volunteers appear in columns B and C in different ways, such as
David Chapple
Marjorie and David Chapple
Marjorie & David Chapple
Bill Bloggs, Marjorie & David Chapple
This means that I cannot search for Marjorie Chapple, I must search separately for Marjorie and Chapple.
I have created three further columns as follows
Column D (row 6) contains the formula =IFERROR(FIND("David",C6,1),0)
Column E (row 6) contains the formula =IFERROR(FIND("Chapple",C6,1),0)
Column F (row 6) contains the formula =IF(AND(E6>0,F6>0),1,"")
Column D searches for the name David in the afternoon session for that day and returns 0 if not found and a number>0 if it is.
Column E searches for the name Chapple in the afternoon session and returns 0 if not found and a number>0 if it is.
Column F returns a 1 if both David and Chapple are found for that afternoon session and remains blank if the both names are not found.
I can now write a further three columns to find when David Chapple manned the centre in the morning.
So I now know how many sessions (morning or afternoon) that David Chapple manned the centre.
Now I can create a macro that will do the whole process for me.
My problem is this - I have to repeat the process for all 50 volunteers and the only way I know of doing this is to manually change the names in the macro for each of the volunteers.
As all the names of the volunteers are contained in a separate Excel table (one column for first name and another for the family name) I would like to know how I can automatically import the names into to my macro so that it can find out the necessary information for all 50 volunteers?
Column A contains sequential dates as in a calendar
Column B contains the one, two or (occasionally) three names of volunteers that manned the Visitor centre that morning
Column C contains the one, two or (occasionally) three names of volunteers that manned the Visitor centre that afternoon.
There are about 50 volunteers that will appear in columns B and C, some more often than others. The names of the volunteers appear in columns B and C in different ways, such as
David Chapple
Marjorie and David Chapple
Marjorie & David Chapple
Bill Bloggs, Marjorie & David Chapple
This means that I cannot search for Marjorie Chapple, I must search separately for Marjorie and Chapple.
I have created three further columns as follows
Column D (row 6) contains the formula =IFERROR(FIND("David",C6,1),0)
Column E (row 6) contains the formula =IFERROR(FIND("Chapple",C6,1),0)
Column F (row 6) contains the formula =IF(AND(E6>0,F6>0),1,"")
Column D searches for the name David in the afternoon session for that day and returns 0 if not found and a number>0 if it is.
Column E searches for the name Chapple in the afternoon session and returns 0 if not found and a number>0 if it is.
Column F returns a 1 if both David and Chapple are found for that afternoon session and remains blank if the both names are not found.
I can now write a further three columns to find when David Chapple manned the centre in the morning.
So I now know how many sessions (morning or afternoon) that David Chapple manned the centre.
Now I can create a macro that will do the whole process for me.
My problem is this - I have to repeat the process for all 50 volunteers and the only way I know of doing this is to manually change the names in the macro for each of the volunteers.
As all the names of the volunteers are contained in a separate Excel table (one column for first name and another for the family name) I would like to know how I can automatically import the names into to my macro so that it can find out the necessary information for all 50 volunteers?
