Select a portion of mirror column data in formulas

[CardiganJefe (Jeff)]

Using {mirrorName} in a formula copies the aggregate cell data. Can I grab only pieces of it?

For example if a mirror column aggregates 3 sources of data, say item quantities (Red-12, Green-6, Blue-19), can a formula identify only the red source? For example {mirrorName#Red} and show 12 in the formula column?

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Jeff,

Try:

IF(SEARCH("Red",{Mirror#Names},1)>0,
    REGEXEXTRACT(
        RIGHT({Mirror#Names},
        -3+LEN({Mirror#Names})-IF(SEARCH("RED",{Mirror#Names},1) > 0, SEARCH("RED",{Mirror#Names},1),LEN({Mirror#Names}))
        ),
    "^\d+"),
"Not Found")

 

---

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

• Create formulas without using the Formula column (and avoid its limitations)
• Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
• In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

[CardiganJefe (Jeff)]

Thanks for the reply Gilles. I tried implementing the solution and found I did not explain my problem exactly because I kept getting formula errors. I’ll attempt again with a picture.

I would like to identify mirrored data via the Name (ex: “Red” and “Blue”) and grab the data, in this case numbers 1 and 2, to display in a formula column.

Your last solution was elegant in querying the Connect column, yet I could not re-engineer a solution based on your work yet, if at all possible.