Hello all,
I hope someone can help. I have a workbook with a few worksheet. Each worksheet represents an item, and each item is serialized, so each worksheet is a list of all serialized items.
For example worksheet named Radios. In column A is a list of all serial numbers for Radios. Column B is Name
In another workbook, employees have a worksheet with their name on it. In this worksheet is a list containing three items with serial numbers. Their name is also listed in a Cell of this workbook.
What I am trying to do is create a formula that will take a serial number in column A in workbook 1, check all the worksheets in Workbook 2, for that serial number, and if it is found in workbook 2, return the name of the worksheet the serial number is found, to workbook 1, Column B. Here is the formula I put into Column B2
=IFERROR(INDEX('[Staff Inventory.xlsm]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$B$3:$E$3,MATCH(A2,'[Staff Inventory]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$E$11:$F$11,0),1),"no match")
So, Staff Inventory is the worksheet with the names of staff as work sheets. $B$3:$E$3 is the location of the employees name. $E$11:$F$11 is the location of the serial number
So in my mind the formula in Workbook 1, Column B2 says: look at this cell $E$11:$F$11in all worksheets in this workbook Staff Inventory.xlsm. If the serial number matches cell A2, return the contents of $B$3:$E$3. If a match is not found in any of the worksheets, return "no match"
It will not work with a range of worksheets, but will work if I reference a single worksheet. Can someone help? Thanks
I hope someone can help. I have a workbook with a few worksheet. Each worksheet represents an item, and each item is serialized, so each worksheet is a list of all serialized items.
For example worksheet named Radios. In column A is a list of all serial numbers for Radios. Column B is Name
In another workbook, employees have a worksheet with their name on it. In this worksheet is a list containing three items with serial numbers. Their name is also listed in a Cell of this workbook.
What I am trying to do is create a formula that will take a serial number in column A in workbook 1, check all the worksheets in Workbook 2, for that serial number, and if it is found in workbook 2, return the name of the worksheet the serial number is found, to workbook 1, Column B. Here is the formula I put into Column B2
=IFERROR(INDEX('[Staff Inventory.xlsm]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$B$3:$E$3,MATCH(A2,'[Staff Inventory]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$E$11:$F$11,0),1),"no match")
So, Staff Inventory is the worksheet with the names of staff as work sheets. $B$3:$E$3 is the location of the employees name. $E$11:$F$11 is the location of the serial number
So in my mind the formula in Workbook 1, Column B2 says: look at this cell $E$11:$F$11in all worksheets in this workbook Staff Inventory.xlsm. If the serial number matches cell A2, return the contents of $B$3:$E$3. If a match is not found in any of the worksheets, return "no match"
It will not work with a range of worksheets, but will work if I reference a single worksheet. Can someone help? Thanks