Copying Data From Spreadsheet with VLOOKUP

[cem0cem]

Hello,

First i want to thank everyone for all the help. I have been using this website for a long time and it solved a lot of my problems.

I have been trying to find out a way to copy some data and i couldn't succeed so i am opening this thread.

Problem is:

There is a stock excel that we keep our records and we are trying to update it everyday, after deliveries. Delivery numbers come from a coworker in excel format.

I used VLOOKUP formula and it solved my first problem which is copying only the delivered parts and amounts to our stock excel. But there are 2 problems:
- In false situation, the formula write NA to the cell and that breaks abstracting formulas. If i got 10 parts and there is no delivery that day i still have 10 but NA break this since 10 - NA = NA
- Other problem is I want to keep only the exact values because i will delete de second delivery excel after my work is done. so after getting the number with vlookup, i must delete the formula and only the exact number should stay.

So my working look up formula is like: =VLOOKUP(A2; [delivery.xlsx]Temp1!$A$2:$C$15; 2; FALSE)

My main stock and dispatch excels are like the photos i attached.

My goal is;

- Copying the numbers from dispatch excel without opening it with =VLOOKUP(A2; 'C:\Users\Lenovo\Desktop/[delivery.xlsx]Temp1'!$A$2:$B$15; 2; FALSE) formula on my main excel.
- giving 0 value to NA cells
- Making the numbers exact so they won't change.

I can copy the formula to my main excel colums manually, if you can help me with the things mentioned above i would be greatful.

 

[jasonb75]

To fix the #N/A! problem, you need to wrap your vlookup in IFERROR, for example =IFERROR(VLOOKUP(A2,...,FALSE),0)

To keep the values of the formulas when the source data is changed, you would need to copy and pastespecial values.

 

[cem0cem]

Hello Jasonb75, thank you very much for your reply.

I tried to use IFERROR before opening this thread but i get an error ( problem formula). I tried your version too but still not working. My office version is 2015.

 

[jasonb75]

Something I missed in your post, you need to change the comma to semicolon.

=IFERROR(VLOOKUP(A2;...;FALSE);0)

naturally, the rest of your existing formula needs to go in place of ...

 

[cem0cem]

Jasonb75, thank you very much. Problem solved with the comma change so formula is working now.

I added a buton to update the link so people can get data from another excel. I used;

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

So i have 2 questions now;
1- The code written above update all the work book, is there a way to update just one column?
2- After the update, excel want's you to select the folder for updating the link but i have already wrote the specific file adress in the formula so is there a way to prevent this selection and allow excel to update from the adress by itself?

 

[jasonb75]

Not that I'm aware of.

I've previously used find and replace (Ctrl h in excel) to edit the formulas rather than update the links, maybe that is something that you could utilise?

For future reference, when you have a question where 2 parts are significantly different (as you have here, 1 part formula and another part vba) it is better to start a thread for each part. If the parts of your question are quite similar then they can often be answered in the same thread. Not everyone understands all areas of excel and many people are reluctant to only provide partial answers.

 

[cem0cem]

You are right, maybe i should stop writing this thread and start another for my VBA questions because formula is working and i only need help for VBA.

I managed to update just one column than deleting it's formula so values would be constant. I'm writing below in case of someone looking for a smillar code;

 ActiveWorkbook.UpdateLink Name:="xxx.xlsx", Type:=xlExcelLinks
    ActiveWorkbook.BreakLink Name:="xxx.xlsx", Type:=xlExcelLinks