Indirect funtion

MFish

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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.
 
Upvote 0
You are welcome.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top