1

I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:

[enter image description here]

As shown above, I have the master sheet, and two filtered sheets based on the column "admission". To filter the entries based on admission, I used the FILTER function

=FILTER('Master sheet'!A$2:$D$20,'Master sheet'!$D$2:$D$20="accept",FALSE)

[enter image description here]

=FILTER('Master sheet'!A$2:$D$20,'Master sheet'!$D$2:$D$20="reject",FALSE)

enter image description here

Note that I added notes to the filtered sheets. Is it possible to link the entries in the "notes" column of the master sheet based on id?

For example, I want to link the entry of column E2 in the Filter_Accepted sheet to column E2 in the Master sheet sheet based on the id 743. Part of the formula in column E of Master sheet should contain this:

=IF(Filter_Accepted!A2 = 'Master sheet'!A2, ...) *I can't figure out how to link the cells*

In the actual dataset, there are thousands of entries, and the entries change dynamically, so I don't think using = to link the cells will do the trick. The point is, the data from the "notes" column in the filtered sheets should be linked to the data in the Master sheet (matched on id), and when the entry in the Master sheet is deleted, the column will also be gone. Is this possible?

This is the desired output: enter image description here

Thank you very much for the help

#p.s.: I can't use VBA since I'm using Excel Online

1 Answer 1

0

This isn't the most dynamic or elegant solution, but it should do the job for your current set-up. A formula for 'Master Sheet'!E2 that (I think) does what you want is this (a formula without structured references and named ranges is below).

The only deficiency I can think of here, is if you delete a row from the master sheet, that won't trigger the deletion of that particular row's notes cell in the filtered sheets, and so then the "notes" that you'd get would be off by one, since now a new ID is where that old ID was in the filtered sheet, but the new ID still has the old notes. I don't think there is a way to handle that particular scenario (at least, not without VBA), so I'd use this approach to get the master sheet cleaned up, and then use the master sheet, with filters, to do any future edits to notes and removals from the master list.

=IF(
    [@admission] = "accept",
    INDEX(
        Filter_Accepted!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Accepted#,
                0,
                1
            )
            ,0
        )+1,
        5
    ),
    INDEX(
        Filter_Rejected!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Rejected#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Basically, we use an IF to search one of the two sheets, and use INDEX along with MATCH to get the cell that has the information we want. You can jump down to the bottom for a commented version that explains what the bits and pieces are doing.

You'll also notice I've got structured references and named cell ranges in here. [@admission] could be replaced with a relative reference to D2, [@id] could be replaced with a relative reference to A2, and the Accepted# and Rejected# named references could be replaced by Filter_Accepted:$A$2# and Filter_Rejected:$A$2#, respectively.

That formula would look like this:

=IF(
    D2 = "accept",
    INDEX(
        Filter_Accepted!$1:$1048576,
        MATCH(
            A2,
            INDEX(
                Filter_Accepted!$A$2#,
                0,
                1
            ),
            0
        )+1,
        5
    ),
    INDEX(
        Filter_Rejected!$1:$1048576,
        MATCH(
            A2,
            INDEX(
                Filter_Rejected!$A$2#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Explanation of Formula

=IF(
    ' Check whether master table admission column, for this row (@), is accept
    [@admission] = "accept",
    ' it is accept. Use INDEX to get the cell with the notes for
    ' this row's id
    INDEX(
        ' search the entire Filter_Accepted sheet
        Filter_Accepted!$1:$1048576,
        ' find the row of the Filter_Accepted sheet that has the relevant ID.
        MATCH(
            ' the ID we're searching for.
            [@id],
            ' where we're searching for that ID. In this case,
            ' we're looking at all rows (0), of the first column (1),
            ' of the dynamic array returned by the `FILTER` for accept.
            INDEX(
                Accepted#,
                0,
                1
            )
            ' Exact match (0)
            ,0
        ' the row number we get will be off by one,
        ' since the `FILTER` dynamic array is in Filter_Accepted:$A$2.
        ' so, add 1 to get the right row number for the whole sheet.
        )+1,
        ' we want the fifth column of the Filter_Accepted sheet, since
        ' that's the one that has the notes.
        5
    ),
    ' it is not accept, so it must be reject
    INDEX(
        ' same story as above, just for the Filter_Rejected sheet
        ' and the dynamic array from the `FILTER` for reject.
        Filter_Rejected!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Rejected#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Example File

Can be found here: https://www.dropbox.com/s/5anilsphdlloh60/superuser-1716190.xlsx?dl=0 (both versions of the formula should work)

4
  • Thanks! This sure does the trick. However, a problem arise when I change the entry in the master sheet, causing the notes to be disorganized. Is there a way to work around this i.e., when I change an entry in the master sheet (from accepted to rejected), the notes get moved to the other sheet along with the other filtered entries? #p.s.: I'll have to work on the notes on the filtered sheets, so I don't think I can work centrally on the master sheet Anyway, thank you very much! Apr 14 at 6:37
  • 1
    Ultimately, since you don't have control of the main sheet, you'll need to store the association between id and notes elsewhere at some point in your flow and let that be the source of truth. E.g., make notes on filtered sheets, copy values for id and notes to another sheet. Then, clobber the notes in the filter sheets. When you get updates in the main sheet, add notes to the filtered sheet again then copy them to the source-of-truth sheet again. You can use de-dupe on the source-of-truth to get rid of dupes according to rules. Apr 14 at 6:47
  • 1
    You could also use a similar formula to pull in existing notes from the source-of-truth sheet on the filter sheets while you work them. I'd keep a date column handy and put that in the source of truth too. You can then get rid of older notes when you dedupe the source of truth. Apr 14 at 6:49
  • I see,, thanks very much for the help! Apr 14 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.