Indirect funtion

MFish

Board Regular
Joined
May 9, 2019
Messages
71
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,163
Office Version
365
Platform
Windows
Pleace this is cell A2 and copy down:
Code:
=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

Board Regular
Joined
May 9, 2019
Messages
71
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

Board Regular
Joined
May 9, 2019
Messages
71
EDIT:

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,163
Office Version
365
Platform
Windows
You are welcome.
 

MFish

Board Regular
Joined
May 9, 2019
Messages
71
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?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,163
Office Version
365
Platform
Windows
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:
Code:
[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:
Code:
[COLOR=#333333]=AND(ISODD($A1),ROW()>1)[/COLOR]
which will always evaluate to FALSE for row 1.
 

MFish

Board Regular
Joined
May 9, 2019
Messages
71
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,163
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,194
Messages
5,412,980
Members
403,459
Latest member
acharnp

This Week's Hot Topics

Top