It seems I can find a million similiar post to my issue but each one is just different enough to not be able to help me. I regularily download 2-5 excel documents from the company I work for. Each of these contains different data about jobs I must track. They all have a common Job number column that I want to use to create an excel(or any office program, just can't get access to work for me) table with.
I have (hopefully I'm right) learned from the forum that vlookup is the best command for this task. I setup (over about 20 hrs, excel noob!) one workbook with sheets from all 2-5 sources and got them to display all on one tracking sheet. However, I cannot use it going forward because updating the source files is killing the old data. I tried just cutting and pasting new excel sheets onto the original source files (at the bottom of chart) but this is also failing because sometimes the information in other columns changes and I need my tracking sheet to do so as well. From what I have searched over the last month I am begginning to think I have reached a limit in this command or maybe even excel?
I cannot send the actual data due to confidentiallity but I will try to emulate it here:
Sheet1: tracker sheet (as it displays, formula below)
job number______date_____AmtPaid_____Invoice#____Address:
123____________1/1/11___$1_________5______15 N. 1st
234____________1/3/08___$4_________2______12 N. 8th:
345____________1/4/10___$2_________1______10 N. 12th.
sheet2: Billing Report (from company)
Job number __AmtPaid ___Inv#
345 _________$2________1
234 _________$4________2
123 ________ $1_______ 5
sheet:3 Work Report (from different department in comapny)
EngineerDate:_________date____Job#____Address_____Date.(copy date for vlookup)
1/25/1985____________1/1/11___123____15 N. 1st ____=a2
1/1/1980_____________1/3/08___234____12 N. 8th ____=a3
11/01/2000___________1/4/10___345____10 N. 12th___=a4
Sheet1 has the job numbers I enter and then all the other columns resemble: (names and #s don't match example above, but the sheet works so I know they are valid):
=IF(ISNA(VLOOKUP([PurchaseOrder],Work[[#All],[Purchase Order]:[Column4]],5,FALSE)),"",VLOOKUP([PurchaseOrder],Work[[#All],[Purchase Order]:[Column4]],5,FALSE))
Of course the vlookup values differ from column to column. Amazingly enough the forums got me to a sheet that works, and ommits NA if the job hasn't been billed yet. But then next week's sheet2 arrives and I can't make it right. New sheet2 has (for example) two more jobs that need to be added and one of the first 3 has changed invoice numbers. If I paste the new chart at the end of old sheet2 the job that changed INV# doesn't get updated (also tried removing duplicates first but it removed the new entry instead of the old). If I delete sheet 2 entries and paste in new ones then all of the data already pulled from vlookup (using the entries I just deleted) goes back to blank.
It seems this procedure is more convoluted than automated. Usually this means I am using the wrong function or program but Google isn't very helpful if you don't know the jargon to search for!
I was thinking another if statement could settle the updated invoice number problem but couldn't tackle setting up 2nd Vlookup to search below previous (I mean to say table array could only include the part I pasted) but this would require rewriting the formula weekly.
I have been told database would be more simple but have found several posts saying it cannot handle a dynamic source?
I have (hopefully I'm right) learned from the forum that vlookup is the best command for this task. I setup (over about 20 hrs, excel noob!) one workbook with sheets from all 2-5 sources and got them to display all on one tracking sheet. However, I cannot use it going forward because updating the source files is killing the old data. I tried just cutting and pasting new excel sheets onto the original source files (at the bottom of chart) but this is also failing because sometimes the information in other columns changes and I need my tracking sheet to do so as well. From what I have searched over the last month I am begginning to think I have reached a limit in this command or maybe even excel?
I cannot send the actual data due to confidentiallity but I will try to emulate it here:
Sheet1: tracker sheet (as it displays, formula below)
job number______date_____AmtPaid_____Invoice#____Address:
123____________1/1/11___$1_________5______15 N. 1st
234____________1/3/08___$4_________2______12 N. 8th:
345____________1/4/10___$2_________1______10 N. 12th.
sheet2: Billing Report (from company)
Job number __AmtPaid ___Inv#
345 _________$2________1
234 _________$4________2
123 ________ $1_______ 5
sheet:3 Work Report (from different department in comapny)
EngineerDate:_________date____Job#____Address_____Date.(copy date for vlookup)
1/25/1985____________1/1/11___123____15 N. 1st ____=a2
1/1/1980_____________1/3/08___234____12 N. 8th ____=a3
11/01/2000___________1/4/10___345____10 N. 12th___=a4
Sheet1 has the job numbers I enter and then all the other columns resemble: (names and #s don't match example above, but the sheet works so I know they are valid):
=IF(ISNA(VLOOKUP([PurchaseOrder],Work[[#All],[Purchase Order]:[Column4]],5,FALSE)),"",VLOOKUP([PurchaseOrder],Work[[#All],[Purchase Order]:[Column4]],5,FALSE))
Of course the vlookup values differ from column to column. Amazingly enough the forums got me to a sheet that works, and ommits NA if the job hasn't been billed yet. But then next week's sheet2 arrives and I can't make it right. New sheet2 has (for example) two more jobs that need to be added and one of the first 3 has changed invoice numbers. If I paste the new chart at the end of old sheet2 the job that changed INV# doesn't get updated (also tried removing duplicates first but it removed the new entry instead of the old). If I delete sheet 2 entries and paste in new ones then all of the data already pulled from vlookup (using the entries I just deleted) goes back to blank.
It seems this procedure is more convoluted than automated. Usually this means I am using the wrong function or program but Google isn't very helpful if you don't know the jargon to search for!
I was thinking another if statement could settle the updated invoice number problem but couldn't tackle setting up 2nd Vlookup to search below previous (I mean to say table array could only include the part I pasted) but this would require rewriting the formula weekly.
I have been told database would be more simple but have found several posts saying it cannot handle a dynamic source?