Forum Discussion
Comparing multiple cells, if one cell has value, then name this cell another cell
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.
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
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
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)
