I have multiple cells in a table containing concatenated data ( 2/8 ) ranging from H:AL.
I would like to be able to highlight the cells if the numbers added together are greater than a number in column D

Thanks in advance

Please attach a sample sheet of your data.

What do you mean the cells contain "concatenated data"?

So let me get this straight, you want to highlight the entire range H:AL if the sum of those cells is greater than D. Is that right?

As you will see in the sample i have found a way by adding a formula into the actual cell that returns a addtional symbol to the cells if the added values are above the critriea . This meaning i can conditional format with a strring search.

I dont like how lengthy the formula is and am open to suggesttions

Attached Files

Sample.xlsx (20.7 KB, 7 views)

i used CONCATENATE to show 2 values in the same cell example ( 2 / 4 ) but i wish to add the 2 number together and checkvaule agaisnt another cell

You want all the cells where the two concatenated numbers are greater than D when summed together. Is that right?

So in your sample file, just those cells which you have denoted with the single quote on row 3, right? And that single quote is not necessary for your purposes once we figure this out correctly, right?

correct the single quote is only for a search string to conditional format

How about this? I formatted all your concatenated data cells to text. I also copy and pasted text only all your data because it was looking for a file on your desktop to get data from. So just look at the conditional formatting and replicate on your sheet if you it suits your needs.

Basically, the conditional format equation is:

Of course, this will only work if you only have single digit concatenation. If that assumption is not valid, then we can modify the equation to account for it:

Attached Files

Sample.xlsx (16.1 KB, 6 views)

yes i have tried to use this system (i also tried to use this format to and the single quote into the cell but that caused a circular ref).How can this formula be put into a conditional format so it checks each cell by its only reference?

Select the entire range and put the formula in. It should apply accordingly.

this is the bit thats confussing me. When i chose conditional format by formula and input (=LEFT(H4,FIND("/",H4)-1)+RIGHT(H4,LEN(H4)-FIND("/",H4))>$D4) as the criteria , when i change which cells it applies to ( columns H:AL) all cells are looking to H4 for the result?

So long as you have properly used the $ as I have to only anchor column D as absolute, the formula should apply properly even if it doesn't reflect it in the dialog.

If you have a question as to whether it's working, test it out by changing some of the values in D in the lower rows.

just tried and not having any sucess. Also when i try it in a single cell (H4) it highlights the cell breifly when i lower d4 but then returns to no fill?

Please attach your sheet with the new formatting.

sample as requested

Attached Files

Sample.xlsx (20.8 KB, 8 views)

Unless I'm not understanding what you are trying to do, it seems to be working fine. When D6 is 4, no concatenated cells in row 6 are highlighted. When I change it to 3, all of the 0/4 cells become highlighted. Nothing happens in to the cells in any other row. Likewise if I change the D value in any other row.

Of course, you cannot have the single quotes in the concatenated fields so I'm not quite sure why you still have those.

And you may need to format the concatenated cells as text. Excel might think they are dates.