INDEX Seperators, Does Such A Thing Exist ?

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
hi all,

you've been very helpful for answering my last questions, but this one has popped up and i dont understand very much of it ( i found the code somewhere in the forums and thought it would be useful )

=INDEX(Results!A:A,MATCH(9.99999999999999E+307,Results!A:A),1)*1000

this will return the value of the cell in the last row of the Column "A"

is it possible to include some type of modification to this code to make it find the last value, but search no further than say 500 rows down the column ?

and also how to make it return the last cell's value between 500-1000

it'd be very handy to have for multiple values on the same column, as i have large amounts of data divided into sections, and would like a report to include the last entry for each section on a page

:)

thanks everybody
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you need a way to change the full column references (A:A) to just the subset of the rows you are interested in - one way is to use indirect():

with start & end rows in c2 & c3 respectively, the altered formula would look like this:

=INDEX(INDIRECT("A"&C2&":A"&C3),MATCH(9.99999999999999E+307,INDIRECT("A"&C2&":A"&C3),1))
Book7
ABCD
12All21
23Betweenrow1
34androw10
45Result7
56
67
7
8
9
10
1112
1213
1314
1415
1516
1617
1718
1819
1920
2021
Sheet1


Paddy
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
it'd be very handy to have for multiple values on the same column, as i have large amounts of data divided into sections, and would like a report to include the last entry for each section on a page

If you want to persist with this method, how about:

=INDEX(INDIRECT("$B$1:"&ADDRESS(MATCH(D3,A:A,0)-1,2,1,1)),MATCH(9.99999999999999E+307,INDIRECT("$B$1:"&ADDRESS(MATCH(D3,A:A,0)-1,2,1,1)),1))

See the example:
Book7
ABCDE
1Section12LastPerSection:
23Section17
34Section216
45Section325
56END
67
7
8
9Section212
1013
1114
1215
1316
14
15
16Section321
1722
1823
1924
2025
21END
22
Sheet2


The only caveat is that you have to put and END marker in col A below the final row of data in col B & below the last section heading in the table.
 

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110

ADVERTISEMENT

that looks ok, but picture that setup for this sheet i have here with just data going from row 1 to row 650 all across to AM.

and then another 8 sheets like that.

cleaning it is slow, but im getting there

:)
 

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
i've just used

=INDEX(INDIRECT("B"&1&":B"&650),MATCH(9.99999999999999E+307,INDIRECT("B"&1&":B"&650),1))


so i specify where to look, inside the cell that displays the last value

cause the range is always going to be the same
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-08 17:48, Elemental wrote:
hi all,

you've been very helpful for answering my last questions, but this one has popped up and i dont understand very much of it ( i found the code somewhere in the forums and thought it would be useful )

=INDEX(Results!A:A,MATCH(9.99999999999999E+307,Results!A:A),1)*1000

this will return the value of the cell in the last row of the Column "A"

is it possible to include some type of modification to this code to make it find the last value, but search no further than say 500 rows down the column ?

and also how to make it return the last cell's value between 500-1000

it'd be very handy to have for multiple values on the same column, as i have large amounts of data divided into sections, and would like a report to include the last entry for each section on a page

:)

thanks everybody

What kind of cues are available to determine where a given section starts and ends?
 

Forum statistics

Threads
1,144,310
Messages
5,723,632
Members
422,505
Latest member
Noar33

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
Top