Format formula reference to different sheet

[icissel1234]

Ok so I have a workbook with several sheets, this question focuses on two sheets. 'Coded Prehospital Data' and 'Missing Pre Hospital Data', for brevity here i will refer to them as coded and missing respectively.

In coded the cells are populated using an index/match fx to another sheet and if that returns an error the fx looks to the same cell in missing for a value

Here is an example of the formula in coded cell C2:

=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))),ISBLANK(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))),VLOOKUP('Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))

Without having to constantly switch between sheets I would like to set up a conditional format that fills a cell in missing yellow when that cell is an error in coded.

This is what I have done to achieve this with no success:

1) Select cell C2 in missing and add rule based on formula
2) Enter =OR(ISNA('CODED PREHOSPITAL DATA'!$C2),ISBLANK('CODED PREHOSPITAL DATA'!$C2))
3) Enter the custom formatting I decided on
4) In the "Applies To" box I have done two things: 1) drag the cursor from c2 to an28 which auto fills the applied to dialogue box with 'Missing Prehospital Data'!$C$2:$AN$28 and 2) Free type in the dialogue box 'Missing Prehospital Data'!$C2:$AN28

My problem is that I need the formatting for each cell in missing to refer to its sister cell in coded but it continues to refer only to coded c2

Any wisdom is appreciated

 

[Jerry Sullivan]

Hi icissel1234,

The problem isn't that the Conditional Formatting(CF) rule formula isn't referencing the sister sheet: ~"Coded".

The unexpected results are because the resulting value from the formula in Coded!$C2 isn't blank or an error. If the IsBlank OR IsError test fails, the formula in C2 does a vlookup. Provided that the results of the vlookup are not blank or an error, there's no way for the CF formula on sheet Missing to determine directly from the value in Coded!$C2 whether the Index-Match or Vlookup is returning the displayed result.

One fix would be for the CF formula to perform the same Index-Match test that the formula in Coded!C2 is doing...
=OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE))),ISBLANK(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE))))

A downside of this is if changes are made to the formula in Coded!C$2, it's possible that the tests will no longer be equivalent.
A more reliable approach would be to use 1-2 helper column(s) that would return the results of the Index-Match and validation tests.

The helper column could be in either worksheet and hidden if you prefer. Assuming you use Columns BA and BB of sheet Missing here are some example formulas:

In Missing!BA2, then copy down...
=IFERROR(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE)),"")&""

In Missing!BB2, then copy down...
=OR(ISERROR(BA2),BA2="")

The CF Rule formula in Missing can be simply...
=$BB2
Applies to: $C2:AN28

In Coded!C2, then copy down...
=IF('Missing Pre Hospital Data'!BB2,VLOOKUP('Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),'Missing Pre Hospital Data'!BA2)

 

[icissel1234]

Jerry thanks for the info, I'm a newb so sorry for the follow up questions if you don't mind:

The problem isn't that the Conditional Formatting(CF) rule formula isn't referencing the sister sheet: ~"Coded".

The unexpected results are because the resulting value from the formula in Coded!$C2 isn't blank or an error. If the IsBlank OR IsError test fails, the formula in C2 does a vlookup. Provided that the results of the vlookup are not blank or an error, there's no way for the CF formula on sheet Missing to determine directly from the value in Coded!$C2 whether the Index-Match or Vlookup is returning the displayed result. The purpose of the CF is to know that i have to select something for that vlookup to occur so when the the cell in coded is error or blank it looks to missing and performs the vlookup but at that point missing will be and empty cell with list validation. Does that change anything?

One fix would be for the CF formula to perform the same Index-Match test that the formula in Coded!C2 is doing...
=OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE))),ISBLANK(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE)))
This seems like the easiest option at this point but if I use this as the cf formula won't it be true regardless of whether an error, blank, or real value is returned?

A downside of this is if changes are made to the formula in Coded!C$2, it's possible that the tests will no longer be equivalent.
A more reliable approach would be to use 1-2 helper column(s) that would return the results of the Index-Match and validation tests.

The helper column could be in either worksheet and hidden if you prefer. Assuming you use Columns BA and BB of sheet Missing here are some example formulas:

In Missing!BA2, then copy down...
=IFERROR(INDEX(MATCHAGEUNIT,MATCH('Coded Pre Hospital Data'!$A2,MATCHCC,FALSE)),"")&""

In Missing!BB2, then copy down...
=OR(ISERROR(BA2),BA2="")
^^Does this mean that i would have to do two helper columns per 'normal' column?

The CF Rule formula in Missing can be simply...
=$BB2
Applies to: $C2:AN28

In Coded!C2, then copy down...
=IF('Missing Pre Hospital Data'!BB2,VLOOKUP('Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),'Missing Pre Hospital Data'!BA2)[/QUOTE]

 

[Jerry Sullivan]

The purpose of the CF is to know that i have to select something for that vlookup to occur so when the the cell in coded is error or blank it looks to missing and performs the vlookup but at that point missing will be and empty cell with list validation. Does that change anything?

That explanation helps. I didn't realize you wanted the Vlookup to return blank until the lookup in Column D of sheet Missing was filled in by the user in response to the highlighted row.

With that understanding, the problem with the CF formula you were trying is that the ISBLANK() function only returns True if a cell is Empty. If the cell has a formula that evaluates to a Null String "", then ISBLANK() will turn False.

Since you are trying to test if the formula in C2 is returning a Null String, use a test of the expression: C2=""

Also since an Index-Match that finds a empty cell typically returns a zero, try modifying your formula in Coded!C2 to return a Null string instead of a zero like this...

=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE) )=""),VLOOKUP('Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))&""

With that change, this CF formula should work in Missing...
=IF(ISERROR('Coded Pre Hospital Data'!$C2),TRUE,IF('Coded Pre Hospital Data'!$C2="",TRUE,FALSE))

No helper columns are needed for this approach, although if you placed the result of your Index-Matches in a helper column and referenced that from the formula in Coded!C2, it would be more efficient than evaluating that expression three times within the formula.