List numbers to maximum number but excluding certain numbers.

[S.H.A.D.O.]

Good afternoon, I hope everybody had a great Christmas.

In cell M2 I enter a maximum whole number.
Now in cells M3:M5 there will be another whole number(s), but possibly not in all three. These numbers will not be higher than the maximum whole number.
What I would like is a formula in cell B4 and continuing down to list the numbers from 1 to the maximum number in cell M2 but without the numbers in cells M3:M5 being included please. The first number could be number 1 so the list would start with number 2. The last number could be the maximum number so the list would end with the maximum number LESS 1.

So for example, if the maximum number in cell M2 is 15, and the numbers in cells M3:M5 are 4, 8, & 14, the list will produce the numbers:-

01
02
03
05
06
07
09
10
11
12
13
15

I hope this makes sense.
Thanks in advance.

 

[Trebor76]

The only thing is that it shows numbers past the maximum number in cell M2

Yes - you must manually copy it down to the last cell to match what the maximum number is in cell M2 (see my earlier comments).

As mirabeau said you're spoilt with options here.

Regards,

Robert

 

[T. Valko]

I would be interested in a formula solution please.
The maximum number would probably be no higher than 60.
I am using Excel 2007.
Thanks in advance.

Create this named formula:

Name: Numbers
Refers to: =ROW(INDIRECT("1:"&$M$2))

Then, enter this array formula** in B4:

=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,M$3:M$5,0)),Numbers),ROWS(B$4:B4))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[Snakehips]

S.H.A.D.O. et Al,

I have to admit defeat. I tried all ways to beat my particular approach into submission but I could not get it to solve when any M4:M5 limiter = M3:M4.

IMHO Biff's solution is brilliant!
Every day's a school day!!!!!!

 

[S.H.A.D.O.]

I would just like to say a VERY BIG THANK YOU to everyone who made contributions.

I will go with BIFF's formula for this case scenario but will be able to use the VBA approaches for a couple of the other tasks I am working on, especially as they all give the correct results. I must admit that this was driving me nuts.
This board is absolutely brilliant.
Thanks again.

 

[T. Valko]

I would just like to say a VERY BIG THANK YOU to everyone who made contributions.

I will go with BIFF's formula for this case scenario but will be able to use the VBA approaches for a couple of the other tasks I am working on, especially as they all give the correct results. I must admit that this was driving me nuts.
This board is absolutely brilliant.
Thanks again.

You're welcome. Thanks for the feedback!

 

[S.H.A.D.O.]

Hi Biff,

Just a quick question please.
Is there any way that I can exclude numbers from 10 to 19 and numbers 30 to 39 within the column B Formulas?
I have tried adapting the Formula accordingly but to no avail.
Do you think it will be easier to do this using a VBA Function and then using an If...Then statement within the code that uses the Formula range. I think I will probably be able to do this if you think the Formula root would be a nightmare to achieve.
Thanks in advance!