Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the price
which already saved before in quote file it's giving error till I open the
price list files,

Is there any way to keep result saved in the quote file and not to change
the price till I make refresh to the cell????, same as if I am using only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you dont mind find me other code to get the Result without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody