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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,115
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,115
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,115
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,115
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.
 

Forum statistics

Threads
1,089,616
Messages
5,409,351
Members
403,259
Latest member
Bubba49

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top