to pull the data from other sheet automatically

Put this formula in E7 of Sheet2:

=IFERROR(VLOOKUP(SUBSTITUTE(IF(ISNUMBER(SEARCH(".",$D7)),LEFT($D7,FIND(".",$D7)-1),TRIM($D7))," ",""),Sheet1!$B:$F,COLUMNS($D:E),0),"")

then copy across and down.

Hope this helps.

Pete

That is the challenge "special-k" , in some sheets space are there like you said one example "raw 2" and in some sheets decimal values are there, that is the problem. Like this i have a huge volume of data. Sorry for bothering you, I will try with the formula you shared ad then get back to you. Thanks.

HI Pete_UK,

Your formula works thanks a lot , but if i try to do the same for pulling the data from one workbook to other with huge volume of data it is not working. Do i need to make any other change in the formula other than selecting the data from other workbook???

I don't know what "... it is not working ..." means. In what way is it not working? Do you get error messages, wrong data, what?

The part of my formula that has:

Sheet1!$B:$F

will need to be changed to refer to the other file. You will need to include the full path and the filename if the file is not open.

Hope this helps.

Pete

Hi Pete_UK,

It works fine , that is the reason I updated the status of this post as "Solved". Thank you so much.