Vlookup is finally working but when I update source files I loose data

[Luke0616]

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?

 

[Tinbendr]

I was thinking another if statement could settle the updated invoice number problem...

Are you using dynamic named ranges for your lookup table?

 

[Luke0616]

Thanks for the help David. I am really new to excel terminology, but from what I just learned on Google I think the answer is no. I am however using tables on each of the individual spreadsheets so the ranges are dynamic and I think it is the same thing a different way? Here is what I am using:
=IF(ISNA(VLOOKUP([@[Purchase Order]],Work[[Purchase Order]:[Column4]],9,FALSE)),"",(VLOOKUP([@[Purchase Order]],Work[[Purchase Order]:[Column4]],9,FALSE)))

Where "purchase order" is the column of the tables that vlookup is using and "work" is another spreadsheet in same book.

I have been extending the tables down each week and just copy/pasting new cells in. This causes duplicates in the each sheet so I have to organize the table by the date columns and run the duplicate finder-yuk! Starting to feel like a pen and paper is better solution even though all of the data is coming from my computer.

 

[Luke0616]

Left out an important clue, the cut/paste then duplicate finder is the part that looses the data.

If two weeks ago the data was:

Job#____Inv#____work date
17______2_______3/15/2000

but there was a need to reinvoice it, then this weeks report includes:

Job#____Inv#____work date
17______8_______3/15/2000

so the sheet where I pasted them in has both entries and no real way to distinguish them. It would be more effective to just update the excel document according to this weeks report and write over the data that has updated. But if I paste new data in over the old then the other job loose their vlookup entries. Can Vlookup be done more permanantly where it doesnt go back to blank when the source sheets get deleted?

 

[Tinbendr]

In your named ranges, PO, Col4, and Work, do you have formulas that look like this?

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

This will keep the named range dynamic, so that no matter how many rows you add, Excel will adjust the row count automatically.

 

[Luke0616]

No I dont have it that way. Instead I have charts defined and as long as the data is pasted inside the chart it accepts the new row count. I guess it's not as infinite, but it works the same I think?

 

[Tinbendr]

That was my best guess.

Afraid I'll have to have a sample before I can help further.

 

[Luke0616]

well I am out of ideas, probably easier to spoof my data than figure it out alone, how do I upload a notebook to ya?

 

[Tinbendr]

Check your PM.