Custom filter to select a value and values divisible by that number.

**jad70** · 02-05-2009, 04:07 PM

Hi all,

As I have not tried to do this yet this is a hypothetical scenario.
Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36?

On the attached example sheet, column 'O' will be filtered.

Apologies for not trying to crack this myself but I do not know where to begin.

One more little question, will macros still run in hidden columns?

Many thanks.

JD

**rylo** · 02-05-2009, 07:28 PM

Hi

How about adding a "helper" column and filtering on that. Using your example file, put .5 in U1. In U2 enter the formula

=MOD(O2,$U$1)=0

and copy it down to U817. Now if you put on an autofilter, you can filter on TRUE in column U.

Does that give you some ideas?

rylo

**jad70** · 02-06-2009, 04:53 AM

rylo,

Thanks for the input.
I made a slight mistake on my original post, it is column called 'Due Cal' that needs to be filtered. (row 'N')
I altered your formula to reflect this, then in U1 put in 12. After about 5 minutes, it finally filters all results above 12 but divisible by 12, whereas I want the opposite. If I put in 12, I want to view the row results for 3,6 and 12.
If I can do this simply with a modification of the formula, then perhaps I do not need a macro to run this as I will be selecting the value of U1 from a drop down cell (showing all the filter values) on another sheet. All the user has to do is select a value, say 144, and the 'All Tasks' sheet will display all the tasks for 144 and values divisible into 144.

I will try and play with the formula, but any advice would be great.

Cheers.

JD

**rylo** · 02-06-2009, 07:09 PM

Hi

Try this

U2:

=AND(N2<=$U$1,MOD($U$1,N2)=0)

rylo

**jad70** · 02-14-2009, 05:21 PM

rylo,

apologies for the delay.

Your format works a treat. If I select 144 it shows me all the factors of 144 and I just filter for true.

Many thanks.