mininum with referance

[DT]

Hi,
I have three diff files with the data something like this.
first file
1.xls
12
34
56
78
45

2.xls
21
22
28
35

3.xls
25
66
33
58

i want to use some function which can find the minimum value from each row
of this file and then put the file name of the min value
eg for first row if 12 is the cheapest value , function should return 1.xls

please let me know if this is possible.

 

[Tom Ogilvy]

Is there only one worksheet per file (cells are located on worksheets, not
files)?

Where do you want the results placed. Next to the numbers in 1.xls? where?

Sub CompareBooks()
Dim v as Variant, i as Long, j as Long
Dim sh1 as Worksheet, sh2 as Worksheet
Dim sh3 as Worksheet, lastrow as Long
v = Array("1.xls","2.xls","3.xls")
i = lbound(v)
set sh1 = workbooks(v(i)).Worksheets(1)
set sh2 = workbooks(v(i+1)).Worksheets(1)
set sh3 = workbooks(v(i+2)).Worksheets(1)
lastrow = sh1.cells(rows.count,1).End(xlup).row
for j = 1 to lastrow
if sh1.cells(j,1) <= sh2.Cells(j,1) and _
sh1.Cells(j,1)<= sh3.Cells(j,1) then
sh1.cells(j,2) = v(i)
elseif sh2.cells(j,1) <= sh1.Cells(j,1) and _
sh2.Cells(j,1)<= sh3.Cells(j,1) then
sh1.Cells(j,1) = v(i+1)
else
sh1.Cells(j,1) = v(i + 2)
end if
Next j
end Sub