Conditional Formatting - comparing worksheets
Hi,
I have 2 worksheets - one is a monthly update, the other is last month's data.
I want to highlight the changes on the monthly update sheet using conditional formatting.
I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)
What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?
Last edited by HBF; 12-05-2008 at 04:18 PM.
=ISNA(Match(A1,Jan,0))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
That works - unless there is the same value in a different row on the data being compared.
What formula would I need that would compare one sheet with the other?
For example if A4 does not = A4, then format accordingly.
I tried using this example, but excel won't allow a reference to another worksheet when using conditional formatting.
If you are using still Jan named range...
then try Formula Is: =A1=INDEX(Jan,ROW())
where A1 is top most cell in your selected range and Jan is the list in the reference sheet.
Trying to show the differences between the two sheets...
Using =isna=E4=INDEX(Feb,ROW()) - this doesn't work. What would be the inverse of the equation to show the condition only if different?
Can you reiterate exactly what you want to have happen... say it in words instead of trying to formulate formulas....
There is a monthly update to a spreadsheet for costing reasons.
I need to compare the last month's report to the new report and highlight the changes.
The reports are formatted the same.
So, unless there is a better way, I thought I would compare cell B2 on the new report to cell B2 on last month's report. If it is the same, nothing happens. If it is different, the font would show red.
You can reference the other sheet with Indirect()...
e.g. your conditional format formula for cell B2 would be something like:
=B2<>INDIRECT("sheet2!B"&ROW(B2))
where Sheet2 is the name of the sheet you are comparing against
Sorry for being so confused about this.
=B2<>INDIRECT("sheet2!B"&ROW(B2))
What is the B for in "sheet2!B" ?
What would it be for C4 - =C4<>INDIRECT("sheet2!C"&Row(C4)) ?
Yes the "B" represents column B... it is hard-coded..
So if you want to compare column C to Column C on other sheet, change the "B" to "C" in the formula as you did.
I guess if you want to make it more dynamic.. so that it automatically checks the relevant cell, whether you are in column B or C... then try: =B2<>INDIRECT(ADDRESS(ROW(B2),COLUMN(B2)))
assuming you are selecting a range to highlight with upper-left most cell in the selection being B2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks