2

I regularly encounter an issue on Excel 2010 when I create a new rule using a formula. The issue is reproducible on several of my computers. I select several cells (for instance A4:B143) and then create a new rule using one of the following formulas:

=$A4="issue"
=($A4="issue")

then press Enter. The goal is, of course, to see all the rows of which the first cell's value is "issue". But this does not work and when I go to "manage rules", I see that Excel has the following formula:

=$A1048441="issue"

This is the formula that I want but not with the number that I entered. If I manually replace this new formula that comes out of nowhere (in my eyes) with my old formula, everything works correctly and the formula stays =$A4="issue".

Does anyone know what might cause this issue?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Alexandre d'Entraigues
  • 330
  • 1
  • 4
  • 11

2 Answers2

3

Please try selecting from top left to bottom right rather than vice versa.

Without the anchor ($) for rows (has to be left off for the one formula rule to apply throughout the range) the row references are relative - but relative to the active cell rather than to the top row.

The active cell when selecting a range is the one that starts the range selection. So a range that is selected from B143 to A4 has B143 as the active cell (not A4). Relative to that Row4 is 139 rows earlier. For CF, 139 rows earlier than A4 is Row1048441 in Excel 2010 (the rows 'wrap' - the last row, Row1048576, is in effect immediately before Row1, so Row1048441 + 136 gets back to Row1 and plus another 3 from there to get to Row4. 136+3 is the 139 difference).

pnuts
  • 58,317
  • 11
  • 87
  • 139
-1

I was able to do apply it in the following manner:

When I entered the formula, I did not put an = sign before. I just put $a6=issue

Rules:

enter image description here

Data:

enter image description here

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • For some reason, that does not seem to work: when I enter `$A5=issue` it is replaced automatically by `="$A5=issue"` which does nothing, and when I enter `$A5="issue"` it is replaced by `="$A5=""f"""` which also does not work. When I then replace the formula by `=$A20="issue"`, it works. I really do not understand whence those " come. – Alexandre d'Entraigues May 27 '15 at 14:57
  • ah.. try putting in `$A5="issue"` – Raj More May 27 '15 at 15:31
  • Do you have multiple things that make up the condition for formatting? – Raj More May 27 '15 at 15:31
  • I had tried `$A5="issue"` as well and it added a lot of quotes in my formula. I tried the solution @pnuts offered and it worked. Thanks a lot for trying to help! – Alexandre d'Entraigues May 28 '15 at 08:55