I have been working on a spread sheet will I can search multipleworkbook run reports by entering the search data in J7. I have it working greatwith the attached formula, this is working with only one workbook 'C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!. I have tried to modify formula to add more file locations for theother workbook with no luck. I am not sure this would be the best way to dothis even if I was able to get this working.
{=IF(M2=0,"",IF(ISERROR(INDEX('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$K$536,SMALL(IF('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536=$J$7,ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536)),ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!1:1)),1)),"",INDEX('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$K$536,SMALL(IF('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536=$J$7,ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536)),ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!1:1)),1)))}
Thanks,
Mark
{=IF(M2=0,"",IF(ISERROR(INDEX('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$K$536,SMALL(IF('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536=$J$7,ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536)),ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!1:1)),1)),"",INDEX('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$K$536,SMALL(IF('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536=$J$7,ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!$A$1:$B$536)),ROW('C:\Users\mgkb\Documents\[smrunreport.xlsm]PartNumber'!1:1)),1)))}
Thanks,
Mark