Conditional Formatting using a formula - how to keep the formula stable

**phrankndonna** · 04-03-2012, 07:23 AM

Hi. I'm using Excel 2010. I've got two sheets where the same data is transcribed from paper and entered on both sheets. One person enters it on Sheet1, and a second person enters it on Sheet2. This is done to ensure accuracy and integrity of that data conversion to electronic format.

I have a conditional formatting formula set up in each sheet whereby Sheet1 looks at Sheet2 (and vice versa), and if anything is not the same on the opposite sheet, the cell turns yellow. Here is the conditional formatting formula in Sheet1: =NOT(EXACT(A1,'Sheet2'!A1)). And, I have the formula applying to =$A:$IV. The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)).

This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconcilliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area.

Is there a way within the Conditional Formatting to prevent all of this and apply the formula statically to the entire worksheet no matter what is copied/cut/pasted/deleted? I tried using static references (i.e., $A$1), but that didn't seem to work. I thought about VBA to reset the formula when the workbook is saved, and I believe that's possible, but each workbook actually has between 6 and 12 sheets (i.e., between 3 and 6 compares). If the formula was the same on each sheet, it would be very simple, but it changes based on the name of the sheet to the right or left of it.

Thoughts? Thank you for your consideration.

Frank

**dilipandey** · 04-03-2012, 08:09 AM

Hi Frank,

I got your point.

We can use offet / lookup function in that case.. for example. in Sheet 1, 8th row from headings need to be matched with 10th row from heading in sheet 2.

share the sample file if you agree with the example I shared. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**phrankndonna** · 04-03-2012, 01:21 PM

Actually, that's not quite what I'm talking about. Each cell in Sheet1 one has to match exactly each cell in Sheet2 (and vice versa). The formula when initially set on the worksheet works fine and does what I'm asking. However, once any kind of manimpulation is done to the sheet (e.g., cut or delete or paste data), then the formula gets skewed as well as the area that it is applied to.

How do I post a workbook?

Frank

**dilipandey** · 04-03-2012, 01:57 PM

Hi Frank,

To upload a workbook, click on Go Advanced while replying to this post and then look for a paper clip icon to upload. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**phrankndonna** · 04-03-2012, 04:04 PM

Thank you for the guidance. I've attached the .xlsx file here. I appreciate your time looking at this.

Frank

**dilipandey** · 04-04-2012, 01:21 AM

Hi Frank,

It is done on the first two tabs... i.e., Male 1 and 2..
Review the attached file. try :-
1) changing the values for "Tissue/Organ" on both the sheets... the moment they differ, the background color will change.

2) Change their row.. i.e, insert/ delete few rows to change their original place.. and you will find still there is no impact

Regards,
DILIPandey

<click on below 'star' if this helps>

**phrankndonna** · 04-04-2012, 08:00 AM

Hi DILIPandey,

First of all, thank you for taking your time to look at this! I opened the file and tried adding data, and it was fine on Males (2) if the data was entered in B10 or B14 (Tissue/organ). But that was the only cell this seemed to work on, and when I go into Conditional Formatting>Manage Rules, it does show the rule applies to only $B$14. Also, it's not a specific value somewhere on the opposing worksheet that needs to match (which is what I believe the VLOOKUP formula is doing), but rather, I need each specific cell to match the other (i.e., A1 on Sheet1 needs to match exactly A1 on Sheet2, and vice versa). I also need this to apply to every cell on the whole worksheet. Also, when I tried deleting column B, it deleted the rule, or when I inserted a column, it pushed the formula to column C.

I'm not 100% certain yet, but I believe I'm going to need a programmatic (VBA) solution to this issue. I appreciate you trying, and if you can think of anything else, I'm certainly open to suggestions. Thanks again!

**dilipandey** · 04-04-2012, 01:54 PM

Hi Frank,

Yes.. I have applied the formula for one metric only... just to prove my point.. and yes, similarly it can be applied on other metrics as well..
if you want to compare cell by cell (untouched after inserting /deleting rows and columns) then still we can use offset function (which will reference top row(1:1) and left most column (a:a) hence these two should not be deleted) but if you see that this can be deleted then only option is vba (macros). thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**phrankndonna** · 04-05-2012, 04:04 PM

Hmmm, I don't think either Row 1 or Column A would ever be deleted. Other full columns and rows, yes, but not the very first column or rows as they contain the header information. Ok, so would the formula be something like this: =A1<>VLOOKUP($A1,'Males (2)'!$A$1:$A$1,2,FALSE)? There may be multiple items in column A with the same value, so I can't have it looking at anything except the specific cell. And, what region would I need to enter for it to apply to? I entered this, and I could not get it to work. Thanks again for working with me on this.

Frank

**dilipandey** · 04-06-2012, 02:00 AM

Hi Frank,

If Row 1 and Column A would not be deleted, than we can lookup values against each metric (like "tissue / organ") and would check if any value against similar metric is changed on other sheets or not.. and basis this we can apply conditional formatting logics.. let me know if you agree with this, so that we can proceed. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**bebo021999** · 04-06-2012, 02:48 AM

Try this apply for every cells in "Study List (1)":

Please Login or Register  to view this content.

Same for "study List (2)" sheet and change (2) to (1) in the formula.

**phrankndonna** · 04-06-2012, 09:31 AM

Hi, thanks for the suggestion. I plugged this in, and could not get it to work. Additionally, every time I entered text in the worksheet, it was taking a few seconds for it to calculate and be able to move on. Finally, in the Rule Manager window, I had the rule 'Applied to' =$A:$IV. In this aspect, this formula was a bit better, but even still, if I inserted or deleted columns, the area to which the formula was applied adjusted. I.E., when I inserted a column, the application area shifted from $A:$IV to $A:$IW. It's primarily this aspect that is throwing things off even with the original formula I entered.

I would think there would be a way to refer to a static set of cells on another sheet (or ALL the cells) and not have it adjust when rows or columns are inserted or deleted. Thanks!

Frank

**phrankndonna** · 04-06-2012, 09:34 AM

For DILIPandey, yes that is correct. Thanks!

**martindwilson** · 04-06-2012, 09:49 AM

wouldn't index be better? =INDEX(Sheet2!1:65536,ROW(),COLUMN())

**phrankndonna** · 04-06-2012, 12:10 PM

I've been trying to apply this, but can't seem to make it work. It does seem to make the 'Applies to' behave a little better, so if I can possibly get this right, it might work. I've attached the workbook again here. Sheets 1 and 2 contain the attempt with the Index, whereas Sheets 3 and 4 contain the initial formula. Thanks!

Frank

**phrankndonna** · 04-10-2012, 07:30 AM

Can anyone tell me if there is a way to LOCK DOWN that 'Applies to' field?

I've played around a lot with this, and i've looked all over the internet, and I'm just not sure I will ever be able to get this to do what I need with the current capabilities of Excel. It is indeed frustrating, because this conditional formatting formula and process would significantly alter and improve the way we do this task here at work.

The issue isn't with the actual Conditional Formatting formula, but rather with the 'Applies to' area. The conditional formatting formula is indeed looking at a second sheet, but the applies to area is looking at the current sheet. If any rows or columns in the other sheet being referred to in the formula get inserted or deleted, then the 'Applies to' field changes. Oftentimes a subsequent rule with the new 'Applies to' range gets added.

I do hope there is a way, but I'm not feeling overly optimistic at the moment. Thanks.

Conditional Formatting using a formula - how to keep the formula stable

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Re: Conditional Formatting using a formula - how to keep the formula stable

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions