Macro to Change Formula

I need a Macro that will initiate a change in the formula in a column anytime the value in a corresponding cell changes in another column.

Explanation:

Column A

1
1
1
2
3
3
3
3
4


Column Q has this formula

=INDEX(FA_Split_Master_July1007!$A$1:$C$57670,SMALL(IF(FA_Split_Master_July1007!$A$1:$A$57670=Sheet1!$T2,ROW(FA_Split_Master_July1007!$A$1:$A$57670)),ROW(1:1)),3)

I am using the above formula in column Q which I have autofilled down.

When I use the Autofill what happens is that the Row numbers in the formula increase.

That is fine as long as the value in column A stays the same.

What I need is a macro that can detect a change in Value in column A, and automatically reset the Row Number in the formula to 1:1

EG ROW(1:1)

HOPE SOMEBODY CAN HELP.

Thanks

Hi

Don't think you need a macro. Try the following formula

=INDEX(FA_Split_Master_July1007!$A$1:$C$57670,SMALL(IF(FA_Split_Master_July1007!$A$1:$A$57670=Sheet1!$T2,ROW(FA_Split_Master_July1007!$A$1:$A$57670)),COUNTIF($A$2:A2,A2)),3)

It should restart the SMALL count for every change in the number.

Does assume that the data in column A will be sorted in ascending order.


rylo

Rylo I cant thank you enough. I struggled with the formula combination for this for 2 days straight. Its working perfectly. I made sure to sort the column in ascending order just like you said.

Thanks Man