need a function that will work using multiple work books and sheet
 

need a function that will work using multiple work books and sheet
 

need a function that will work using multiple work books and sheet

To reference a cell from another workbook, use something like:
'[FFFF]SSSS'!$B$4
where FFFF is the file name (e.g., test.xls) and SSSS is the sheet name.

For another sheet in the same workbook, it's simply:
=SSSS!$B$4

If that doesn't answer the intended question, provide more information
about the current data setup and what you're trying to accomplish.