dynamic address for the range of data

[masoud12]

I have a column of numbers as first row of the range (in “M” column) and another column of numbers as last row number of the range (in N column) in an excel file for the specified column of data (as J column). I want to write the maximum in this range (e.g. J(M2):J(N2) (m2 and n2 are4 row numbers for j column)) in the same row that first and last row of rang specified in another column( column O).

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure that I understand exactly what you have or what you want. Could you give us a small set of dummy data, fill in the expected results manually, post that with XL2BB as mentioned below and explain again in relation to that sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[masoud12]

sample.xlsx
	A	B	C	D
1	ranges	min row	max row	result
2	0	2	3	3010300012189
3	3010300012189	2	3	3010300012189
4	3010300020176	4	4	3010300020176
5	1060207550000	5	6	1060207550000
6	1060207550000	5	6	1060207550000
7	3010300021410	7	8	3010300021410
8	3010300021410	7	8	3010300021410
9	9010109380000	9	9	9010109380000
10	3010300022158	10	11	3010300022158
11	3010300022158	10	11	3010300022158
12	0000000000000	12	12	0000000000000
13	3010300020266	13	13	3010300020266
14	0	14	15	3010300022097
15	3010300022097	14	15	3010300022097
Sheet1

 

[Peter_SSs]

Glad you got XL2BB going but unfortunately, there is nothing shown. You need to select in your worksheet the range of data that you want to show us before you click Mini Sheet.
Also, don't forget these two points

fill in the expected results manually, ........ and explain again in relation to that sample data?

 

[masoud12]

i have three first columns i want to have the forth column. that is maximum number in the range determined in the column B and C

 

[Peter_SSs]

Unfortunately I cannot copy from that image to test with your values (XL2BB is better).

However, from what I can see, this could be what you want.

23 07 30.xlsm
	A	B	C	D
1
2	0		3	6
3	6		3	6
4	8		4	8
5	5		6	4
6	4		6	4
7	3		8	1
8	1		8	1
9	7		9	7
masoud12

Cell Formulas
Range	Formula
D2:D9	D2	=INDEX(A:A,C2)

 

[masoud12]

thank you very much.
I hope the best for you.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

 

[masoud12]

but i know see that in your example A7>A8 so the result should be A7 or 3 but your function get the A8 or 1 as the result.

 

[Peter_SSs]

but i know see that in your example A7>A8

.. but is my data realistic? I just made up random numbers. Which is why we would rather deal with your sample data via XL2BB

It doesn't look like your sample data has anything like that - or does it?

In columns B & C do you have formulas?