Auto fill out exact matching data for multiple entries from another sheet.

[ryan4646]

Hi,


This is Ryan again. I have a sheet that my junior staffs mistakes so much for wrong data input. I have decided to put it in this way that when ever I will write the data in one sheet, exact matching information from another sheet will appear in the specific columns.


Like, I have inserted the information in terms master data sheet. In the summery sheet if I input any vendor name, exact matching information of trade term and payment terms will automatically appear in summery sheet’s “R” and “S” column.


In the summery sheet there is already a vba code inserted for updating the date reason. Please help me to solve the above issue.


This is file is here on the below link.


http://www.mediafire.com/download/cro7tjfvbyo835j/HELP_FILE.XLS

 

[SirJB7]

Hi, ryan4646!


Try this:

R4: =SI.ERROR(BUSCARV($C4;VendorTable;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP($C4,VendorTable,2;FALSE),"")

S4: change 2 by 3

and copy down as required.


VendorTable is a dynamic named range defined as:

=DESREF('temrs master data'!$B$3;;;CONTARA('temrs master data'!$B:$B)-1;CONTARA('temrs master data'!$2:$2)-1) -----> in english: =OFFSET('temrs master data'!$B$3,,,COUNTA('temrs master data'!$B:$B)-1,COUNTA('temrs master data'!$2:$2)-1)

so to be able of adding new entries below those of the sample without having to neither change formulas nor ranges. Take care only of the -1 in both rows and columns count: for rows is because of titles, for columns is because of cell W2. Adjust them as required.


Just advise if any issue.


Regards!

 

[ryan4646]

hi sirjb7,


thanks for your prompt reply. Actually i am a newbie in this kind of advanced formulas. i have tried, its not happening. the first formula vendortable means the first sheet or second sheet, i have a slight confusion. i am failing to do it.


would you mind to put the formula there and upload it for me ? sorry for the request.


Ryan

 

[SirJB7]

Hi, ryan4646!

Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Auto%20fill%20out%20exact%20matching%20data%20for%20multiple%20entries%20from%20another%20sheet.%20-%20HELP%20FILE%20%28for%20ryan4646%20at%20chandoo.org%29.xlsm

If you don't use an Excel version 2007+ just advise to convert it.

Regards!

 

[ryan4646]

hi sirJb7,


i would like to show the below link that i am facing now.


http://www.mediafire.com/download/5xi4xxwop0v6mwv/helpjpg.JPG


The way i want the file is like the link below,supplied by dave turton. where i input any information in main data, it changes upon criteria.can i make the file like that. i am sorry i am very weak in excel so i need you guys help. it will mean a lot for me.


https://docs.google.com/file/d/0ByiZqUlQMjxhY3cyOUF0RXlXWm8/edit?usp=sharing

 

[SirJB7]

Hi, ryan4646!


This is the file form versions 2003-:

https://dl.dropboxusercontent.com/u/60558749/Auto%20fill%20out%20exact%20matching%20data%20for%20multiple%20entries%20from%20another%20sheet.%20-%20HELP%20FILE%20%28for%20ryan4646%20at%20chandoo.org%29.xls


It replaces the IFERROR function available in 2007+ versions for a combination of IF & ISERROR, so the formula posted upwards should look like:

=SI(ESERROR(BUSCARV($C4;VendorTable;2;FALSO));"";BUSCARV($C4;VendorTable;2;FALSO)) -----> in english: =IF(ISERROR(VLOOKUP($C4,VendorTable,2,FALSO)),"",VLOOKUP($C4,VendorTable,2,FALSO))


Regards!