Hi everybody. i need to return a date from another workbook based upon the
cell reference in the first workbook. the formula needs to be able to search
4 sheets in the second workbook for an exact match to the reference in the
first book and then return the date (which is in the column prior to the
match)
can this be done? thank you in advance for any help.

Can the match be anywhere in the worksheet(s), or is it limited to certain
ranges?

Bernie


"new_to_vba" wrote in message
...
Hi everybody. i need to return a date from another workbook based upon the
cell reference in the first workbook. the formula needs to be able to
search
4 sheets in the second workbook for an exact match to the reference in the
first book and then return the date (which is in the column prior to the
match)
can this be done? thank you in advance for any help.

the match is always in column "B" of the second workbork in all sheets.

Nick

"Bernie Deitrick" wrote:

Can the match be anywhere in the worksheet(s), or is it limited to certain
ranges?

Bernie


"new_to_vba" wrote in message
...
Hi everybody. i need to return a date from another workbook based upon the
cell reference in the first workbook. the formula needs to be able to
search
4 sheets in the second workbook for an exact match to the reference in the
first book and then return the date (which is in the column prior to the
match)
can this be done? thank you in advance for any help.

Using worksheet functions:

=IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet1'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet2'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet3'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet4'!$B:$B,FALSE)),"Not Found",INDEX('[Other
Book.xls]Sheet4'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet4'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet3'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet3'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet2'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet2'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet1'!$A:$A,MATCH(A5,'[Other Book.xls]Sheet1'!$B:$B,FALSE)))

Note that there are 3 references to each sheet (for a total of 12) that need
to be updated, with the file name and sheet names. It looks for the value
that is currently in cell A5 of the same sheet as the formula.

IF you want a macro, then it is, of course, a different solution, but just
as easy. Let us know if you were looking for a VBA macro solution.

HTH,
Bernie
MS Excel MVP

"new_to_vba" wrote in message
...
the match is always in column "B" of the second workbork in all sheets.

Nick

"Bernie Deitrick" wrote:

Can the match be anywhere in the worksheet(s), or is it limited to
certain
ranges?

Bernie


"new_to_vba" wrote in message
...
Hi everybody. i need to return a date from another workbook based upon
the
cell reference in the first workbook. the formula needs to be able to
search
4 sheets in the second workbook for an exact match to the reference in
the
first book and then return the date (which is in the column prior to
the
match)
can this be done? thank you in advance for any help.

thanks Bernie, i will try this now but would also like to have a macro if
posible.
the more i can learn the better, and a macro might make it a bit more fool
proof.
(as they might delete formulas b axxident when using this.
thank you very much for your help. Nick

"Bernie Deitrick" wrote:

Using worksheet functions:

=IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet1'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet2'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet3'!$B:$B,FALSE)),IF(ISERROR(MATCH(A5,'[Other
Book.xls]Sheet4'!$B:$B,FALSE)),"Not Found",INDEX('[Other
Book.xls]Sheet4'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet4'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet3'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet3'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet2'!$A:$A,MATCH(A5,'[Other
Book.xls]Sheet2'!$B:$B,FALSE))),INDEX('[Other
Book.xls]Sheet1'!$A:$A,MATCH(A5,'[Other Book.xls]Sheet1'!$B:$B,FALSE)))

Note that there are 3 references to each sheet (for a total of 12) that need
to be updated, with the file name and sheet names. It looks for the value
that is currently in cell A5 of the same sheet as the formula.

IF you want a macro, then it is, of course, a different solution, but just
as easy. Let us know if you were looking for a VBA macro solution.

HTH,
Bernie
MS Excel MVP

"new_to_vba" wrote in message
...
the match is always in column "B" of the second workbork in all sheets.

Nick

"Bernie Deitrick" wrote:

Can the match be anywhere in the worksheet(s), or is it limited to
certain
ranges?

Bernie


"new_to_vba" wrote in message
...
Hi everybody. i need to return a date from another workbook based upon
the
cell reference in the first workbook. the formula needs to be able to
search
4 sheets in the second workbook for an exact match to the reference in
the
first book and then return the date (which is in the column prior to
the
match)
can this be done? thank you in advance for any help.