Trouble with the ROWS function

[Excel_VBA]

The values in A7 and A8 are 1004. In cell D8 I have the following formula:

=IF(A7+1=A8,"AA",ROWS(ADDRESS(ROW(A8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1)))

The formula evaluator shows the ROWS function evaluating to ROWS(A$7:A8) which then evaluates ultimately to 1 instead of 2 rows.

In E8 I entered ROWS(A$7:A8) and it is returning 2 like it should.

So why is the ROWS function returning only 1 instead of 2, when I use it in the IF statement? Does it have something to do with the part that has &":"&?

 

[T. Valko]

The values in A7 and A8 are 1004. In cell D8 I have the following formula:

=IF(A7+1=A8,"AA",ROWS(ADDRESS(ROW(A8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1)))

The formula evaluator shows the ROWS function evaluating to ROWS(A$7:A8) which then evaluates ultimately to 1 instead of 2 rows.

In E8 I entered ROWS(A$7:A8) and it is returning 2 like it should.

So why is the ROWS function returning only 1 instead of 2, when I use it in the IF statement? Does it have something to do with the part that has &":"&?

Not sure what you're trying to do with that but try it like this:

=IF(A7+1=A8,"AA",ROWS(INDIRECT(ADDRESS(ROW(A8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1))))

 

[Excel_VBA]

Not sure what you're trying to do with that but try it like this:

=IF(A7+1=A8,"AA",ROWS(INDIRECT(ADDRESS(ROW(A8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1))))

Thank you. That worked! I totally forgot about the INDIRECT function. Thank you again.

I want to copy this down a few cells so that the ROWS function will have a Absolute reference:Relative reference. The purpose is to use ROWS as an internal counter.

 

[T. Valko]

Thank you. That worked! I totally forgot about the INDIRECT function. Thank you again.

I want to copy this down a few cells so that the ROWS function will have a Absolute reference:Relative reference. The purpose is to use ROWS as an internal counter.

OK, if you want the ROWS count to increment as you copy down then you'll have to use this version:

=IF(A7+1=A8,"AA",ROWS(INDIRECT(ADDRESS(ROW(A$8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1))))

Why can't you just use:

=IF(A7+1=A8,"AA",ROWS(A$7:A8))

 

[Excel_VBA]

OK, if you want the ROWS count to increment as you copy down then you'll have to use this version:

=IF(A7+1=A8,"AA",ROWS(INDIRECT(ADDRESS(ROW(A$8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1))))

Why can't you just use:

=IF(A7+1=A8,"AA",ROWS(A$7:A8))

Thank you for your reply. I can't use the first suggestion because I can't hardcode the '8' due to the fact that I have to copy this formula to cells above and below row 7. The code you have would work fine for row 7, but when I copy up to row 6 and below to row 8, the "-1" part will throw things off.

Maybe I should have given some more values in column A. Cell A4 = 1002, A5 = 1003, A6 through A10 = 1004 and A11 = 1005.

The final results from D7 through D10 should be 1, 2, 3 and 4.

Basically I am looking at numbers in column A. If I see a series of repetitive numbers (A6 through A10), in column D, I want to list the first instance with 1, the second instance with 2, the third occurrence with 3, etc. But I want this to start the first time a number DUPLICATES, i.e. on row 7 not 6.

The problem I keep running into, is trying to find a way to lock a row reference the first time I see a duplicate. So this formula has to take into account numbers on rows above and below. Most numbers in column A are consecutive, but some do repeat. And these repetitions are what I am focusing on.

This is driving me nuts. I'm sure there is a way to do this w/o VBA.

 

[T. Valko]

Thank you for your reply. I can't use the first suggestion because I can't hardcode the '8' due to the fact that I have to copy this formula to cells above and below row 7. The code you have would work fine for row 7, but when I copy up to row 6 and below to row 8, the "-1" part will throw things off.

Maybe I should have given some more values in column A. Cell A4 = 1002, A5 = 1003, A6 through A10 = 1004 and A11 = 1005.

The final results from D7 through D10 should be 1, 2, 3 and 4.

Basically I am looking at numbers in column A. If I see a series of repetitive numbers (A6 through A10), in column D, I want to list the first instance with 1, the second instance with 2, the third occurrence with 3, etc. But I want this to start the first time a number DUPLICATES, i.e. on row 7 not 6.

The problem I keep running into, is trying to find a way to lock a row reference the first time I see a duplicate. So this formula has to take into account numbers on rows above and below. Most numbers in column A are consecutive, but some do repeat. And these repetitions are what I am focusing on.

This is driving me nuts. I'm sure there is a way to do this w/o VBA.

Still don't understand what you're wanting to do but...

The values entered in column A have nothing to do with the row/column references within the ADDRESS function:

=IF(A7+1=A8,"AA",ROWS(ADDRESS(ROW(A8)-1,COLUMN(A8),2,1)&":"&ADDRESS(ROW(A8),COLUMN(A8),4,1)))