At work I have two files, one is an invoice (commissaryinvoice.xlsm) that about 10 co-workers use and the second is a file (icash.xlsx) that I pull daily from a separate program that has account numbers in column A , names in column B and account balances in column H. I use vLookup to pull the names and account balances when their account number is entered into the invoice. Both files are stored on a network drive (J and everyone is instructed to copy and paste a fresh commissaryinvoice.xlsm each morning to their desktop because I frequently make changes to it that reflect out of stock merchandise. On the master copy that is located on the network drive the vlookup formula is
=iferror(vlookup(K2,'J:\Commissary\[icash.xlsx]sheet1'!A1:H2000,2,0),"")
however when the file is copied to the desktop, the formula changes to
=iferror(vlookup(K2,'C:\users\name\desktop\[icash.xlsx]sheet1'!A1:H2000,2,0),"")
it still pulls the data from the icash file but it seems to be older data and if I change the icash file, its not reflected on the invoice. For instance, if I change an account balance from $80.00 to $60.00 in icash.xlsx , the invoice will contuine to show $80.00.
My question is, how do I stop it from changing the path and how can I make it refresh immediately so if I make a change to icash, its instantly reflected on the invoice.
=iferror(vlookup(K2,'J:\Commissary\[icash.xlsx]sheet1'!A1:H2000,2,0),"")
however when the file is copied to the desktop, the formula changes to
=iferror(vlookup(K2,'C:\users\name\desktop\[icash.xlsx]sheet1'!A1:H2000,2,0),"")
it still pulls the data from the icash file but it seems to be older data and if I change the icash file, its not reflected on the invoice. For instance, if I change an account balance from $80.00 to $60.00 in icash.xlsx , the invoice will contuine to show $80.00.
My question is, how do I stop it from changing the path and how can I make it refresh immediately so if I make a change to icash, its instantly reflected on the invoice.