Highlight cell A2 and A1 are not equal (not the usual!)

**301** · 10-10-2015, 04:26 AM

Hello all

I am doing a spreadsheet for my company, in which there are 2 sheets.
Sheet 1 contains information from a CSV file.
Sheet 2 is the "formatted" version of that first sheet, with only selective information taken and manipulated.
The reason why I've done this is irrelevant to my question so please do not address it, except in direct context.

The values of sheet 2 are determined by the first with links, i.e. ='Sheet 1'!Z1

In this second sheet I want to highlight the cell A2 when it is NOT equal to A1.
I would normally do this using Condition Formatting > Determine by formula > =A2<>A1 then select a colour.

However this does not work in this case because the actual value of A1 is the link (='Sheet 1'!Z1)

So, how would I go about highlighting the cells that I want?
Is excel just not smart enough to do this?
I am not particularly interested in using scripts, so please don't spend a bunch of time writing a massive script I'm not going to use :)

I have looked at alternatives like creating a third column and simply writing IF statements, but this does not work for the project.
Interestingly, if I do create a third column and type =A1 into it, the conditional formatting works, despite A1 still being a link.

Many thanks in advance,
John.

**Richard Buttrey** · 10-10-2015, 04:45 AM

Hi, and welcome to the forum.

Without seeing the actual workbook I obviously can't comment with certainty. Usually when two numbers look the same but a formula treats them as being different it's because at a very low precision level they aren't the same. e.g. one number might be say 1.0 and the other 1.000000000001. And the usual solution to this is to use say an =ROUND(A1,5) function.

However upload the workbook so that we may see the result in context,.

**301** · 10-10-2015, 05:31 AM

Ah I see. Here is a link to download the spreadsheet.
http://cl.ly/dUOp

EDIT: sorry, I forgot to say what's what in relation to my general post above.
So CPU is Sheet 1, '~CPU~' is Sheet 2, A1 is the unitCost column and A2 is the Cost Ex (manual) column.

**José Augusto** · 10-10-2015, 08:14 AM

Hi
Use this formula in conditional formating
=F2<>INDEX('~CPU~'!$D$2:$D$20,MATCH(B2,'~CPU~'!$A$2:$A$20,0))

Formula:

Please Login or Register  to view this content.

**José Augusto** · 10-10-2015, 08:17 AM

See the file Parts List Export.xlsx
Regards

**301** · 10-11-2015, 12:34 AM

Can you explain what i'm looking at here?

**FDibbins** · 10-11-2015, 12:40 AM

301 Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

**301** · 10-11-2015, 06:06 AM

Alright, I've attached the file to the post. Here is my original question, just updated to be specific now that I'm uploading the file.

Hello all

I am doing a spreadsheet for my company, in which there are 2 sheets.
The first sheet, CPU contains information from a CSV file.
The second sheet, ~CPU~ is the "formatted" version of that first sheet, with only selective information taken and manipulated.
The reason why I've done this is irrelevant to my question so please do not address it, except in direct context.

The values of the sheet ~CPU~ are determined by the first with links, i.e. =CPU!F2

In this second sheet I want to highlight the cell D2 when it is NOT equal to C1.
I would normally do this using Conditional Formatting > Determine by formula > =D2<>C2 then select a colour.

However this does not work in this case because the actual value of C1 is the link (=CPU!F2)

So, how would I go about highlighting the cells that I want?
Is excel just not smart enough to do this?
I am not particularly interested in using scripts, so please don't spend a bunch of time writing a massive script I'm not going to use

I have looked at alternatives like creating a third column and simply writing IF statements, but this does not work for the project.
Interestingly, if I do create a third column and type =C1 into it, the conditional formatting works, despite C1 still being a link.

Many thanks in advance,
John.

**Richard Buttrey** · 10-11-2015, 06:31 AM

Hi,

It works fine for me - see attached

**301** · 10-11-2015, 11:15 AM

Absolute legend mate, thanks!

Highlight cell A2 and A1 are not equal (not the usual!)

LinkBack

Thread Tools

Rate This Thread

Display

Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Re: Highlight cell A2 and A1 are not equal (not the usual!)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If cell D3 equals X and P3 doesnt equal 'z' highlight it

[SOLVED] Highlight specific cells if any of them equal any 25 names

[SOLVED] Highlight a cell based on another cell being equal

Highlight a text within a cell against an equal text in another cell.

[SOLVED] Cond. Formatting - highlight cell w/value if equal value in list

Highlight Row If Equal to Today's Date

[SOLVED] want highlight when all cells in range are equal

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions