how to skip lines with formulas

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
hi all - my source data (below) contains numbers, on another sheet i want to basically "lookup" all of the numbers which are greater than 100. hard to explain, so hoping the tables below help.

this is source data
Handover 2020.xlsx
N
1910
20840824
2116
22840860
2316
24840890
2515
Schedule Import


i need a formula that fills the column like this
Handover 2020.xlsx
N
29840824
30840860
31840890
32
33
Schedule Import


again i hope that makes sense.

source data sheet - Schedule Import
sheet where i have formulas to return values - Week 1

TIA
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,479
Office Version
365
Platform
Windows
Try:

Week 1 sheet
Book1
N
29840824
30840860
31840890
Week 1
Cell Formulas
RangeFormula
N29N29=FILTER('Schedule Import'!$N$19:$N$24,'Schedule Import'!$N$19:$N$24>100)


Schedule Import sheet
Book1
N
1910
20840824
2116
22840860
2316
24840890
2515
Schedule Import
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
Try:

Week 1 sheet
Book1
N
29840824
30840860
31840890
Week 1
Cell Formulas
RangeFormula
N29N29=FILTER('Schedule Import'!$N$19:$N$24,'Schedule Import'!$N$19:$N$24>100)


Schedule Import sheet
Book1
N
1910
20840824
2116
22840860
2316
24840890
2515
Schedule Import
Hi AhoyNC - my version of 365 doesnt yet have FILTER.... sorry but thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,425
Office Version
365
Platform
Windows
See if this is any use

orsm6 2020-05-22 1.xlsm
A
1840824
2840860
3840890
4
 
5
 
Week 1
Cell Formulas
RangeFormula
A1:A5A1=IFERROR(AGGREGATE(15,6,'Schedule Import'!N$19:N$500/(LEN('Schedule Import'!N$19:N$300)=6),ROWS(A$1:A1)),"")
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
See if this is any use

orsm6 2020-05-22 1.xlsm
A
1840824
2840860
3840890
4
 
5
 
Week 1
Cell Formulas
RangeFormula
A1:A5A1=IFERROR(AGGREGATE(15,6,'Schedule Import'!N$19:N$500/(LEN('Schedule Import'!N$19:N$300)=6),ROWS(A$1:A1)),"")
thanks Peter - i have almost gotten this to work. i should have included where the formula would be put. i have it on sheet 'bulk line' starting at range D6.
this is the formula i have now, and the results from it.
formula = =IFERROR(AGGREGATE(15,6,'Schedule Import'!N$18:N$41/(LEN('Schedule Import'!N$18:N$41)=6),ROWS(A$18:A18)),"")
result =
Handover 2020V1.xlsm
D
6826565
7836180
8840824
9871550
Bulk Line


the order of numbers is not quite right, this is the correct order.
Handover 2020V1.xlsm
D
9871550
10836180
11840824
12826565
Sheet1
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
See if this is any use

orsm6 2020-05-22 1.xlsm
A
1840824
2840860
3840890
4
 
5
 
Week 1
Cell Formulas
RangeFormula
A1:A5A1=IFERROR(AGGREGATE(15,6,'Schedule Import'!N$19:N$500/(LEN('Schedule Import'!N$19:N$300)=6),ROWS(A$1:A1)),"")
here is another problem to solve, the column could have more than 6 numbers. when i tried the formula with 8 numbers in column N it would find it
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
here is another problem to solve, the column could have more than 6 numbers. when i tried the formula with 8 numbers in column N it would find it
i solved this one.... changed =6 to >5
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
thanks Peter - i have almost gotten this to work. i should have included where the formula would be put. i have it on sheet 'bulk line' starting at range D6.
this is the formula i have now, and the results from it.
formula = =IFERROR(AGGREGATE(15,6,'Schedule Import'!N$18:N$41/(LEN('Schedule Import'!N$18:N$41)=6),ROWS(A$18:A18)),"")
result =
Handover 2020V1.xlsm
D
6826565
7836180
8840824
9871550
Bulk Line


the order of numbers is not quite right, this is the correct order.
Handover 2020V1.xlsm
D
9871550
10836180
11840824
12826565
Sheet1
i think i just solved this one.... changed AGGREGATE(15 to AGGREGATE(14
 

Watch MrExcel Video

Forum statistics

Threads
1,095,728
Messages
5,446,174
Members
405,389
Latest member
Excel n00b2

This Week's Hot Topics

Top