Referencing cells and columns

[jgd2u]

Good Afternoon,

I have a complicated issue. I have an inventory spreadsheet that has column C:C filled with serial numbers and column D:D needs to be filled with the items condition. The condition status is located on various different sheets that have inventory from our multiple facility locations. So, I need to find the match for C:C across the multiple sheets, and I need the data in the next column over to display. Example Inventory D2 needs to Equal the text data in the immediate cell to the left of C2's match across spreadsheets. I have attached an example.

*Note, this is required for an inventory in the thousands, so it has to be something easy to transpose, which is the issue I am having.

[Crawfinator1]

I copied your sheet 2 and 3 locations into the seperate sheets (i.e. C1:D7 - A1 on Sheet2)(E1:F7 - A1 on Sheet3) and used the following formula

=IF(COUNTIF(Sheet2!A3:A7,CONCATENATE("=",Sheet1!A3))=1,VLOOKUP(Sheet1!A3,Sheet2!A3:B7,2,FALSE),IF(COUNTIF(Sheet3!A3:A7,CONCATENATE("=",Sheet1!A3))=1,VLOOKUP(Sheet1!A3,Sheet3!A3:B7,2,FALSE)))

[dilipandey]

Hi jgd2u,

welcome to the forum.

Try using below formula:-

Please Login or Register  to view this content.

Book1.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[jgd2u]

Crawfinator, works great in the practice book, but I am having trouble getting it to work in the actual spreadsheet. I am attaching the original spreadsheet to my first post for you to see. I have to get the formula to reference the container number, and put the corresponding serviceable block from sheets IAD to Repair on the inventory sheet. Same concept, just a little more complex.

[dilipandey]

You forgot to attach... but that's good.. please include dummy data before attaching again.

And also perform a check on data layout and formulas to conclude why this is working in practice workbook and why not in your workbook - what's the difference between both the workbooks ? thanks.



Regards,
DILIPandey
<click on below * if this helps>

[jgd2u]

Crawfinator, Disregard my previous post. I was missing the fact that you used an array at one point. Have it working, just trying to make it applicable to the last few worksheets and then I will be good to go!

[dilipandey]

Cheers

If your issue resolved, please mark this thread as [SOLVED].. Thanks.


Regards,
DILIPandey
<click on below * if this helps>