Comparing multiple cells, if one cell has value, then name this cell another cell

[Chris2409]

Hi All,

I am tracking manufacturing of items for a project. I have multiple variations of the same object. 

Example: I allocate which office cubicle is needing a particular type of filing cabinet. (one drawer, two drawer, three drawer etc, is needed for "Office A"). 

I am needing to create a formula to check a row of cells, whichever one of these has a value of "1", then pull the name of a corresponding cell. 

The reason I do this is so on other worksheets, when manufacturing the filing cabinets, it lists the needed cabinet, which I determine if I need to pull from inventory, fabricate or purchase. (I also track the various components and object manufacturing status.)

 

The name of the object can change, so I wish for the ability to name the cells in green. 

I have screen captured what I am intending. The formula I am looking for (in layman's terms)

Check cells C5, D5, E5, F5, G5. If any of these =1. Then Name cell B5 as the Text based cell I select.

Any assistance would be appreciated.

 

[#2 · Accepted solution]

@Chris2409 

Hi there!

Try to use this in cell B5:

=OFFSET(INDIRECT(CELL("address",INDEX(C5:G5,MATCH(1;C5:G5,0)))),-2,0)

Change your ranges accordingly. The thing here is that you should change the row offset for each of the B column cells. That is, for B6, you should put -3 in the offset row count.

Martin

[#3]

Thanks Martin. I have put it in. Did get an error to start with, I changed the formal to;
...MATCH(1,C5:G5,0... (comma instead of the semi colon)

[#4]

@Chris2409 

Glad it worked! Sorry for that, though. I use semicolon as a separator and I missed that one!

Cheers,

[#5]

@Chris2409 

A touch of overkill using 365?

= BYROW(required?, LAMBDA(nReq?, 
    TEXTJOIN(", ",, IF(nReq?, nReq? & " x " & cabinateType, ""))
  ))

where

cabinateType
=Sheet1!$C$3:$G$3

required?
=Sheet1!$C$5:$G$6

total
= BYCOL(required?, SUM)