I made a post to the functions DG earlier in the week, thinking that the
issue was related to a function, but I did not get a solution there, and I am
not really sure it is a function-related question€¦I guess it is more of a
programming-related question.

Basically, I am trying to make a reference switch between relative and
absolute, when a value in Column A, sorted in ascending order, changes.

The values in Column A are evaluated in the following way:
If ActiveCell.Offset(-1, 0) < ActiveCell.Offset(-1, -1)

The function in C2 is:
=IF(ROWS(C$2:C2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C2)),"")

The function in C3 is:
=IF(ROWS(C$2:C3)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A3,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C3)),"")

A close inspection reveals that =IF(ROWS(C$2:C2) changes to
=IF(ROWS(C$2:C3), and continues down in this fashion as long as the value in
Column A does not change. The C$2 has to remain absolute, but change to
something like Offset(-1, 0), when the value in Column A changes. For
instance, when the value in Column A does change, in my example it is row 10,
I need the function to change to:
=IF(ROWS(C$10:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEAR CH(A10,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$10:C10)),"")

Then, the value in Column A changes again on Row 11, so the next function
would have to be something like:
=IF(ROWS(C$11:C11)<=B$1,"A"&SMALL(IF(ISNUMBER(SEAR CH(A11,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C11)),"")

I created a Do Loop, and some logic, along with a piece of a recorded macro,
and came up with this (below):

Do Until ActiveCell.Offset(0, -1) = Empty
If ActiveCell.Offset(-1, 0) = ActiveCell.Offset(-1, -1) Then 'And ActiveCell
< Empty And ActiveCell.Offset(1) < Empty Then
Selection.FormulaArray = _

"=IF(ROWS(R2C:RC)<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import
Sheet'!R1C[-2]:R65000C[-2])),ROW('Import
Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C:RC)),"""")"
ActiveCell.Offset(1, 0).Select
End If

If ActiveCell.Offset(-1, 0) < ActiveCell.Offset(-1, -1) Then
Selection.FormulaArray = _

"=IF(ROWS(R10C:RC)<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import
Sheet'!R1C[-2]:R65000C[-2])),ROW('Import
Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C:RC)),"""")"

ActiveCell.Offset(1, 0).Select
End If

Loop


I can't think of a way to get the Offset(0, -1) to be absolute when the
value in Column A changes and then change once again when the values in
Column A change again. I did some research on this DG and I believe the
problem is that I need to refer to a cell, such as this:
KeyCell = ActiveCell.Address(False, True)

I guess this makes the first part of:
ROWS(C$10:C10)
Rows absolute and the second pars to Rows relative. This is great, now I
need to add this to part of my code.


Ive been using Excel, pretty heavy-duty, for over four years now and I have
never encountered something such as this before. Biff gave me a great
function, which does exactly what I want, but only when those ROWS(C$2:C2),
ROWS(C$10:C10), etc. change when the values in Column A change. Has anyone
dealt with this before? I can't help but think that I am making it wayyyy
more difficult that it should be. If anyone has any ideas, please share.


Thanks a bunch!
Ryan--