Filter changing forumla

**sherlock1241** · 02-14-2014, 08:31 AM

Hi

Is there any way of stopping part of a formula changing when a filter is used. I am using*

=RANK(U6,U:U,0)+COUNTIF(U2:U6,U6)-1

When the formula changes, it is all good except I don't want the U2 to change. I want that to always stay as U2 but the rest of it can change with the filter.

Thanks in advance

**ImStevenB** · 02-14-2014, 11:44 AM

Hi sherlock,

If you are copying this formula to different cells/rows, you can force the formula to maintain a specific cell reference using the $.

Set your formula like this:

Please Login or Register  to view this content.

The $ in front of the U will keep it in Column U no matter what column you move the formula to and the $ in front of the keeps the reference in Row 2.

Hope this helps.