Forum Discussion
Highlighting a cell with a Reference error within it that isn't reporting a reference error
I need a way to highlight a cell with a VLookup formula with a reference error within it. When a column on a separate sheet is deleted, the formula has a reference error. It is not reporting a reference error, but because the reference error is within it, it is not functioning correctly. In order to identify when this happens, I need to highlight the cell, but have not found a way. The formula is below:
=IF(ISERROR(VLOOKUP(B8,'13 WK'B:B,1,0))=TRUE,$H$5,$H$4)
where B8 is the cell I am looking for in the other sheet, H5 reports a value if B8 is not found in the other sheet, and H4 reports a value if B8 is found in the other sheet. When column B is deleted on the other sheet the formula is:
=IF(ISERROR(VLOOKUP(B8,'13 WK'!#REF!,1,0))=TRUE,$H$5,$H$4)
However, the output is not a reference error; it is the value of H5. I don't need it to report a reference error, I just need to highlight the cell when there is a reference error within the formula. Is there any way to do this?
Madyson8 You would need something like this:
=IF(ERROR.TYPE(VLOOKUP(B8,'13 WK'!#B:B!,1,0))=4,"Reference error in formula", IF(ISERROR(VLOOKUP(B8,'13 WK'!#B:B!,1,0)),$H$5,$H$4))As soon as column B gets deleted, the formula will display "Reference error in formula". Otherwise it will display either H5 or H4.
More on the ERROR.TYPe function here:
https://support.microsoft.com/en-us/office/error-type-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa
2 Replies
Madyson8 You would need something like this:
=IF(ERROR.TYPE(VLOOKUP(B8,'13 WK'!#B:B!,1,0))=4,"Reference error in formula", IF(ISERROR(VLOOKUP(B8,'13 WK'!#B:B!,1,0)),$H$5,$H$4))As soon as column B gets deleted, the formula will display "Reference error in formula". Otherwise it will display either H5 or H4.
More on the ERROR.TYPe function here:
https://support.microsoft.com/en-us/office/error-type-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa
- Thank you very much!!
