Complex cross-worksheet Excel formula with string manipulation

Question

I am generating an Excel workbook from C# code, but the code will end there as the workbook must be distributable to people who don’t have access to the VSTO stuff.

The workbook contains:

A) “Worksheet 1” that contains a dropdown list which gives “1” or “2” depending on the selected item.

B) “Worksheet 1” also has several cells in a grid layout which currently contain “1”.

C) On “Worksheet 2” there are string values in cells A1 and A2 (which relate to the drop-down items), e.g. C01010101 C01010102

D) On “Worksheet 3” in the same locations as the Worksheet 1 cells (see B above) there are related string values which look like this: (note that these values could be any length)

checkbox|001|C010101010101

checkbox|002|C010101010102

checkbox|002|C010101010103

etc

What I need to do is add a formula to the cells at B so that rather than just containing "1" they result in a value according to the following logic:

1. N1 = selected index (1-2) of drop-down (in A above)

2. S2 = string value from “Worksheet 2”, cell range “A” & N1

3. S3 = the 3rd pipe delimited field value from “Worksheet 3”, from the cell which corresponds with the one in “Worksheet 1” (i.e. same address, C5, B6 etc.)

4. If S2 matches the left end of S3, return “1” otherwise return “0”

Finally, if possible, I would like to colour the cells so that if 0 is returned they have a grey background, but this must be unchanged if someone overtypes the cell manually with a 0.

I don't expect anyone to provide the entire solution - although of course that would be great! - but all suggestions as to how I can achieve this would be gratefully received!

[Edit]

Images attached.

Note that although the output grid is displayed with ticks, these actually contain a "1" if ticked or "0" if unticked.

It's the "1" that the formula will be replacing, because the cell should only be "valid" if the appropriate list-item is selected - if that makes sense :$

[/Edit]

[Edit2]

Ok this is my progress so far:

=IF(LEFT(MID(F17,FIND("|",F17,FIND("|",F17)+1)+1,999),LEN(E17))=E17,1,0)

For this simplified (:o) example F17 is the cell containing the pipe delimited string and E17 contains the drop-down value to match.

Now I need to work out how to reference a range using a row number read from the drop-down...

[/Edit2]

Answer 1

This is the final formula I came up with:

=IF(
    LEFT(
        MID(
            Sheet2!V19,
            FIND(
                "|",
                Sheet2!V19,
                FIND(
                    "|",
                    Sheet2!V19
                )+1
            )+1,
            999),
        LEN(
            INDIRECT(
                "Sheet3!"&ADDRESS(T16,1)
            )
        )
    )=INDIRECT(
        "Sheet3!"&ADDRESS(T16,1)
    ),
    1,
    0
)

where Sheet2 is the sheet containing the pipe delimited strings, Sheet3 column A contains the drop-down lookup values, V19 is the current cell, T16 is the cell containing the drop-down.

As required, this causes the current cell to display 1 if the drop-down lookup matches the third word in the pipe delimited string, or 0 otherwise.

I'm going to the pub...