SOLVED

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

Copper Contributor

# 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.

4 Replies
best response confirmed by Chris2409 (Copper Contributor)
Solution

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

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

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

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)

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

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

Cheers,

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

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)``````
1 best response

Accepted Solutions
best response confirmed by Chris2409 (Copper Contributor)
Solution

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

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