Matching multiple cells in multiple sheets and if no match, it checks information on a 3rd sheet?

[BAQI]

This is a shared worksheet, where people aside from myself, only edit the information on Sheet A.

I'm looking to do the following:

On Sheet A, use the information in cells D3 AND E3 to check for an exact match in A2:A AND B2:B on Sheet C.

For example:

Cell D3 on Sheet A = 123456 Cell E3 = 90

It checks A2:A on Sheet C for 123456.

A4, A19, A92 all have 123456.

It checks B4, B19, and B92 on sheet C to see if any have "90", if it does, it puts "XXXXXX" in cell J3 on Sheet A.

If it doesn't match, then it looks to see if I put anything in cell A1 in Sheet B. If there is nothing, it leaves it blank. If there is something, it puts that value in J3 on Sheet A.

However, what I'm also looking to do is that whenever someone deletes/moves the information in D3/E3, it clears the information I had in J3 on Sheet A.



Now that I wrote this out, I'm wondering if I should approach this another way.

Should there be two different workbooks entirely? The main workbook that displays everything on a monitor for everyone to see, then another workbook for the people who have access to be able to edit, and the main workbook reference the one that can be edited? The reason I'm thinking that is because the one that is displayed regularly has the formatting messed up because of the different proficiencies of the users who have access to the workbook.

The workbook of the one that is displayed could also have formatting setup so it fits perfectly to the monitor that it is on, and look better.

 

[excel_learnerz]

You can achieve the desired result by using a combination of IF, INDEX, and MATCH functions on Sheet A. Here's how you can create the formula:

1. In cell J3 on Sheet A, enter the following formula:

=IF(AND(D3<>"", E3<>""), IFERROR(INDEX(SheetC!$C$2:$C$100, MATCH(1, (SheetC!$A$2:$A$100 = D3) * (SheetC!$B$2:$B$100 = E3), 0)), IF(SheetB!$A$1<>"", SheetB!$A$1, "")), "")

This formula checks if there's a match for the values in D3 and E3 on Sheet C, and if so, it puts "XXXXXX" in J3 on Sheet A. If there's no match, it looks for a value in A1 on Sheet B and puts it in J3 on Sheet A if there is one.

1. To clear the contents of J3 when D3 or E3 are cleared, you can use a VBA code. Press Alt + F11 to open the VBA editor, and double-click on the sheet name where D3 and E3 are located in the "Project Explorer" pane on the left. Paste the following code into the code window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Or Target.Address = "$E$3" Then
If Target.Value = "" Then
Range("J3").ClearContents
End If
End If
End Sub

This code will clear the contents of J3 if either D3 or E3 are cleared.

Regarding your question about using two separate workbooks, it's a good idea if you want to separate the editable version from the display version, and it can help maintain the formatting and appearance of the display version.

To set up the display version to reference the editable version, you can use external references in your formulas. For example, if your editable workbook is named "EditableWorkbook.xlsx", you can reference cell A1 on Sheet1 in that workbook like this:

='[EditableWorkbook.xlsx]Sheet1'!A1

Simply replace the workbook name and the sheet name as necessary for your use case. Note that both workbooks should be in the same folder for this method to work, and the editable workbook should be open while using the display workbook.

 

[BAQI]

You can achieve the desired result by using a combination of IF, INDEX, and MATCH functions on Sheet A. Here's how you can create the formula:

1. In cell J3 on Sheet A, enter the following formula:

=IF(AND(D3<>"", E3<>""), IFERROR(INDEX(SheetC!$C$2:$C$100, MATCH(1, (SheetC!$A$2:$A$100 = D3) * (SheetC!$B$2:$B$100 = E3), 0)), IF(SheetB!$A$1<>"", SheetB!$A$1, "")), "")

This formula checks if there's a match for the values in D3 and E3 on Sheet C, and if so, it puts "XXXXXX" in J3 on Sheet A. If there's no match, it looks for a value in A1 on Sheet B and puts it in J3 on Sheet A if there is one.

1. To clear the contents of J3 when D3 or E3 are cleared, you can use a VBA code. Press Alt + F11 to open the VBA editor, and double-click on the sheet name where D3 and E3 are located in the "Project Explorer" pane on the left. Paste the following code into the code window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Or Target.Address = "$E$3" Then
If Target.Value = "" Then
Range("J3").ClearContents
End If
End If
End Sub

This code will clear the contents of J3 if either D3 or E3 are cleared.

Regarding your question about using two separate workbooks, it's a good idea if you want to separate the editable version from the display version, and it can help maintain the formatting and appearance of the display version.

To set up the display version to reference the editable version, you can use external references in your formulas. For example, if your editable workbook is named "EditableWorkbook.xlsx", you can reference cell A1 on Sheet1 in that workbook like this:

='[EditableWorkbook.xlsx]Sheet1'!A1

Simply replace the workbook name and the sheet name as necessary for your use case. Note that both workbooks should be in the same folder for this method to work, and the editable workbook should be open while using the display workbook.

Thank you for the very thorough response!

I have another question. Is it possible to have the display workbook edit itself in reference to another workbook?

For instance, If Sheet A (that people edit) has 3 rows, and Sheet B (that people edit) has 5 rows on Monday, and the next day Sheet A has 1 row, and Sheet B has 2 rows, can the display workbook add/delete rows automatically and adjust the ratio of the row heights to fill the display that the workbook is on?