Guys I'm thoroughly stuck. This might get complicated, so bear with me.
I have 3 tables, we'll call them A, B, C, each on a different sheet within the same workbook. They're basically staff expenditure logs (the information below is a brief example).
Table A looks like this (the columns start at A (Staff Name):
The dates across the top contain every day of the year.
Table B looks like this:
Table C looks like this:
What I need to do is compare the tables and pull information from tables B and C into table A. I need 2 checks doing:
If the staff name in Column A (Table A) matches an entry in the Staff name column in Table B and the date in Table A, Row 1 falls between the Fin Start Date and Fin End Date on Table B, then pull that financial figure through to table A.
Otherwise, if the Staff Name (column A) and Date (Row1) from Table A match the staff name and date columns in table C then the Spend figure should be pulled through to table A.
With the information above, the result would look something like this:
Because each of the dates on table A fall between the dates on Table B for John Abbott and Bob Smith has one matching entry on Table C.
I have to do this for about 3000 entries on each sheet. I had originally set up formulas for this, but they just gummed up the system and crashed it every time, for obvious reasons.
There must be a better way to do it, but I am crap with VBA.
Any help would be appreciated.
I have 3 tables, we'll call them A, B, C, each on a different sheet within the same workbook. They're basically staff expenditure logs (the information below is a brief example).
Table A looks like this (the columns start at A (Staff Name):
Code:
A B C D E F
1 Staff Name Start Date End Date 01/01/2014 02/01/2014 03/01/2014
2 Bob Smith 13/07/2001 20/11/2014
3 John Abbott 02/09/2002 15/06/2003
etcTable B looks like this:
Code:
Staff Name Fin Start Date Fin End Date Amount
Bob Smith 01/11/2001 30/12/2001 £35
John Abbott 01/01/2014 04/04/2014 £44Table C looks like this:
Code:
Staff Name Date Spend
John Abbott 03/10/2002 £55
Bob Smith 02/01/2014 £66.50What I need to do is compare the tables and pull information from tables B and C into table A. I need 2 checks doing:
If the staff name in Column A (Table A) matches an entry in the Staff name column in Table B and the date in Table A, Row 1 falls between the Fin Start Date and Fin End Date on Table B, then pull that financial figure through to table A.
Otherwise, if the Staff Name (column A) and Date (Row1) from Table A match the staff name and date columns in table C then the Spend figure should be pulled through to table A.
With the information above, the result would look something like this:
Code:
A B C D E F
1 Staff Name Start Date End Date 01/01/2014 02/01/2014 03/01/2014
2 Bob Smith 13/07/2001 20/11/2014 £66.50
3 John Abbott 02/09/2002 15/06/2003 £44 £44 £44
etcBecause each of the dates on table A fall between the dates on Table B for John Abbott and Bob Smith has one matching entry on Table C.
I have to do this for about 3000 entries on each sheet. I had originally set up formulas for this, but they just gummed up the system and crashed it every time, for obvious reasons.
There must be a better way to do it, but I am crap with VBA.
Any help would be appreciated.