how to skip lines with formulas

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
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)),"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top