SOLVED

Format row if formula returns a result

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3157971%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EFormat%20row%20if%20formula%20returns%20a%20result%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3157971%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%20a%20workbook%20with%202%20sheets%20that%20list%20devices%20purchased%20from%202%20different%20companies%20-%20RESTCO%20and%20Conduit.%20There%20are%20500%20rows%20on%20the%20RESTCO%20sheet%20which%20won't%20change%20since%20I'm%20now%20doing%20business%20exclusively%20with%20Conduit.%20The%20Conduit%20sheet%20is%20growing%20as%20I%20am%20managing%20all%20purchases%20through%20them.%20When%20I%20enter%20new%20data%20on%20the%20Conduit%20sheet%2C%20I%20have%20a%20column%20with%20a%20formula%20(shown%20at%20the%20bottom%20of%20this%20post)%20that%20checks%20the%20RESTCO%20sheet%20to%20see%20if%20that%20same%20product%20exists%20on%20the%20RESTCO%20sheet.%20If%20it%20does%2C%20it%20pulls%20the%20%23%20from%20the%20Order%20%23%20column%20and%20puts%20it%20into%20a%20Reference%20column%20on%20the%20Conduit%20sheet.%20This%20lets%20me%20see%20if%20the%20product%20has%20been%20purchased%20before%20and%20find%20the%20details%20more%20easily.%20I've%20been%20adding%20additional%20columns%20to%20the%20Conduit%20sheet%20so%20the%20Reference%20column%20is%20now%20off%20screen.%20I%20figured%20I%20would%20add%20conditional%20formatting%20so%20that%20a%20row%20will%20be%20formatted%20in%20bold%20to%20make%20it%20more%20obvious%20without%20scrolling%20to%20the%20reference%20column.%20But%20I%20can't%20figure%20out%20how%20to%20configure%20the%20condition%20so%20it%20only%20bolds%20if%20the%20formula%20returns%20a%20result.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX('RESTCO'!%24A%242%3A%24A%24501%2CMATCH(B2%2C'RESTCO'!%24B%242%3A%24B%24501%2C0)%2C1)%2C%22%22)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20to%20make%20this%20work%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ETIA%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E~DGM~%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3157971%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Contributor

I have a workbook with 2 sheets that list devices purchased from 2 different companies - RESTCO and Conduit. There are 500 rows on the RESTCO sheet which won't change since I'm now doing business exclusively with Conduit. The Conduit sheet is growing as I am managing all purchases through them. When I enter new data on the Conduit sheet, I have a column with a formula (shown at the bottom of this post) that checks the RESTCO sheet to see if that same product exists on the RESTCO sheet. If it does, it pulls the # from the Order # column and puts it into a Reference column on the Conduit sheet. This lets me see if the product has been purchased before and find the details more easily. I've been adding additional columns to the Conduit sheet so the Reference column is now off screen. I figured I would add conditional formatting so that a row will be formatted in bold to make it more obvious without scrolling to the reference column. But I can't figure out how to configure the condition so it only bolds if the formula returns a result.

 

=IFERROR(INDEX('RESTCO'!$A$2:$A$501,MATCH(B2,'RESTCO'!$B$2:$B$501,0),1),"")

 

Any idea how to make this work?

 

TIA

~DGM~

10 Replies

@DGMalcolm 

Let assume you range is A2:E10000 and formula returns values in column E. Select entire range, add conditional formatting rule with formula =LEN($E2) > 0 and apply Bold format.

best response confirmed by DGMalcolm (Contributor)
Solution

@DGMalcolm 

=NOT(ISNA(INDEX(RESTCO!$A$2:$A$501,MATCH($B2,RESTCO!$B$2:$B$501,0),1)))

An alternative could be this rule for conditional formatting. 

The reference column on the Conduit sheet is R so I opened the Conditional Formatting rule and made the formula: ="LEN($R2)>0". The first row shows '101' which is the correct row from the RESTCO sheet. After applying the rule, nothing gets bolded. What else am I missing?

@DGMalcolm 

Is it applied to entire range?

image.png

Yes, it's applied to everything except the header row like this: =$A$2:$T$101

I've limited my sheet to 100 data rows for now.

@DGMalcolm 

Hard to say without the file. please check my one if you find the difference.

And are you sure you use LEN($R2), not LEN($R$2) ?

@Sergei Baklan 

 

I don't see any difference but it doesn't seem to do what I want. I tried the other suggestion on this thread from @Quadruple_Pawn and it worked.

 

Thank you for your assistance.

 

~DGM~

@Quadruple_Pawn 

 

This worked! Thank you!!

 

~DGM~

@DGMalcolm 

 

=NOT(ISNA(MATCH($B2,RESTCO!$B$2:$B$501,0)))

 

You may skip index function for the conditional formating. 

@Willy Lau  - Thank you for this. Much simpler.