Matching a cell with a multi-select cell

1

Hi

Not actually sure if they are called cells in Coda so I have kept the name from Excel.

Basically, I have a cell that has the content: ‘Flat’ and another table with a multi-select column with the contents: ‘Flat’, ‘Slight-Curve’ and ‘Rounded’

I am trying to create an ‘IF STATEMENT’ where if multi-select matches the word i.e. ‘Flat’ then display the associated cost.

I have managed to do this if both cells have the content: ‘Flat’ but if the multi-select cell has more than one selection it won’t work.

How can I search through the multi-select cell and target an individual selection as I am guessing at the moment, it seeing ‘Flat’ in one cell and ‘Flat’ ‘Slight-Curve’ ‘Rounded’ in the other which is not a match but by taking out the other two selections so the multi-select is just ‘Flat’ it’s obviously a match?

Here’s my current code:

If([Digital Media].[Application Surface].Contains(thisRow.Description),[Digital Media].Filter([Application Surface]=thisRow.Description).[Sales Price (per metre)],"Error")

Thanks

2

I’m not entirely sure what you’re trying to achieve but does this help?

[Table 2].Lookup([Column 1], thisRow.[Column 1]).Cost.IfBlank(0)

3

Thank you that helped.

How would I combine that so that colum 1 and column 2 match column 1 and column 2 on table 2 then show price?

4

I don’t understand. Can you explain in a little more detail or maybe create an example doc without the formula?

5

@Nick_Milner

Give me 5 minutes and I’ll create and simplify a doc to make it easier to understand.

Thanks

6

@Nick_Milner

Apologies, took a bit longer than I thought as I was trying to make things simple and I ended up changing a few things.

Please find attached my doc.

Start with the Dashboard / Quick Quote

I seem to have it all working apart from the cost, it just won’t populate.

7

I believe it’s down to the job types in the media costs table as they are all in one cell.

8

I’ve changed the cost formula to use .contains() rather than = on that part so here’s my formulas:

Old way

If(and([Services Type].Service.Contains(thisRow.Service), [Services Type].[Job Type].Contains(thisRow.[Job Type]), [Services Type].[Job Description].Contains(thisRow.[Job Description])),[Services Type].Filter(and(Service=thisRow.Service, [Job Type]=thisRow.[Job Type], [Job Description]=thisRow.[Job Description])).Cost.sort(), If(and([Manufacturing Type].Service.Contains(thisRow.Service), [Manufacturing Type].[Job Type].Contains(thisRow.[Job Type]),[Manufacturing Type].[Application Surface].Contains(thisRow.[Job Description])),[Digital Media].Filter(and([Job Types]=thisRow.[Job Type], [Application Surface]=thisRow.[Job Description], Brand=thisRow.Brand)).[Sales Price (per metre)],"Error"))

This now seems to work but need to test it.

If(and([Services Type].Service.Contains(thisRow.Service), [Services Type].[Job Type].Contains(thisRow.[Job Type]), [Services Type].[Job Description].Contains(thisRow.[Job Description])),[Services Type].Filter(and(Service=thisRow.Service, [Job Type]=thisRow.[Job Type], [Job Description]=thisRow.[Job Description])).Cost.sort(), If(and([Manufacturing Type].Service.Contains(thisRow.Service), [Manufacturing Type].[Job Type].Contains(thisRow.[Job Type]),[Manufacturing Type].[Application Surface].Contains(thisRow.[Job Description]), [Digital Media].Brand.Contains(thisRow.Brand)),[Digital Media].Filter(and([Job Types].contains(thisRow.[Job Type]), [Application Surface]=thisRow.[Job Description], Brand=thisRow.Brand)).[Sales Price (per metre)],"Error"))