S.H.A.D.O.
Well-known Member
- Joined
- Sep 6, 2005
- Messages
- 1,915
Good afternoon,
In cell E2 I enter a maximum whole number.
Now in cells E3:E7 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 E2 but without the numbers in cells E3:E7 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 E2 is 20, and the numbers in cells E3:E7 are 4, 8, 10, 11 & 14, the list will produce the numbers:-
01
02
03
05
06
07
09
12
13
15
16
17
18
19
20
T.Valko has supplied this solution to my first question which works great.
But, I would like to be able to adapt it so that it also disregards say numbers from 11 to 19 and 30 to 39 in the formulas in cells B4:B? and continuing down.
It might be other numbers but I used the above as an example.
I hope this makes sense!
Thanks in advance.
In cell E2 I enter a maximum whole number.
Now in cells E3:E7 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 E2 but without the numbers in cells E3:E7 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 E2 is 20, and the numbers in cells E3:E7 are 4, 8, 10, 11 & 14, the list will produce the numbers:-
01
02
03
05
06
07
09
12
13
15
16
17
18
19
20
T.Valko has supplied this solution to my first question which works great.
Create this named formula:
Name: Numbers
Refers to: =ROW(INDIRECT("1:"&$E$2))
Then, enter this array formula** in B4:
=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,E$3:E$7,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.
But, I would like to be able to adapt it so that it also disregards say numbers from 11 to 19 and 30 to 39 in the formulas in cells B4:B? and continuing down.
It might be other numbers but I used the above as an example.
I hope this makes sense!
Thanks in advance.