Indirect funtion

[MFish]

Hi there,

I need a code with the INDIRECT Function that will allow me to delete rows and the function won't give me that #REF alert.

Cell A1 is 0
Cell A2, =IF(B2=B1, A1, A1+1)
Cell A3, =IF(B3=B2, A2, A2+1)
So on and so forth... About 400 rows...

I'm "categorizing" the unique values. If it doesn't match then the next line will be a different number, in this case alternating between ODD and EVEN numbers to give me the correct shade through conditional formatting.

 

[Joe4]

Pleace this is cell A2 and copy down:

=IF(OFFSET(A2,0,1)=OFFSET(A2,-1,1),OFFSET(A2,-1,0),OFFSET(A2,-1,0)+1)

It should continue to work, even as you delete rows of data.

 

[MFish]

Hi Joe4,

I have put this in and it doesn't recognize my "Unique ID" or value from the next column over, in this case via OFFSET function. This is, unfortunately, just alternating my numbers giving me alternating shaded rows. I want the same values in column B to be shaded alike.

 

[MFish]

EDIT:

I got it to work, Joe4. Thank you. Much Appreciated.

 

[Joe4]

You are welcome.

 

[MFish]

Now the formula works... My next issue is this...

I have the conditional format that states...

=isODD($A2)

Format to a different shade of color, let's say gray. This is true for the row B2:J2, so and so forth downward. If I were to delete the row, 3, then my conditional format goes bonkers... How can I keep the CF the same when I add/delete rows?

 

[Joe4]

This is true for the row B2:J2, so and so forth downward. If I were to delete the row, 3, then my conditional format goes bonkers... How can I keep the CF the same when I add/delete rows?

It shouldn't go bonkers, if your data and formulas are aligned and the row in your formula is the current row.

It will go "bonkers" if you have offset it by one row.
So, if you select all of column A, and enter the formula:

[COLOR=#333333]=isODD($A2)[/COLOR]

you will be off one row (because in selecting ALL of column A, the first cell is actually A1, not A2, so you are applying that first formula to cell A1).

Basically, here is the rule of thumb to follow:
When writing Conditional Formatting formulas against multi-cell selections, always write the formula as it applies to the very first cell in your selection.
People often get in trouble when the first row is a header, so the first row that they want to apply it to is row 2, but they have selected row 1 (by picking the whole column).
You either need to start your selection in row 2 (not whole column), or adjust your formula.

You can still pick the whole column and have it not apply to row 1 by writing the formula like this:

[COLOR=#333333]=AND(ISODD($A1),ROW()>1)[/COLOR]

which will always evaluate to FALSE for row 1.

 

[MFish]

Hi Joe4,

So, I plugged in that new CF rule and tried deleting an entire row. The CF gets split up right at the row I delete. The CF rule will follow from B2:J16. I'll try to delete row 5 and the CF will now have two rules of... B2:J4 and B5:J16 and will entirely mess up the "color" scheme that I was going for.

 

[Joe4]

Then you might be better off to go a VBA route, and have some VBA code to run upon deletion of data, clears all the CF rules and rebuilds them.