# Range row based on criteria

#### hstef

##### New Member
Hi,

I am trying to max value a range, based on criteria for the range row, and everything I tried so far doesn't work, so your help will be greatly appreciated.
Ex:
Sheet1
A1:A200 = 02:00:00 AM, 03:00:00 AM, 04:00:00 AM, 05:00:00 AM...etc
B1:B200 = values

Sheet2
A1= 04:00:00 AM
B1=Sheet name (Sheet1)
C1= Max formula (=MAX(INDIRECT("'"&B1&"'!"&"B3:B200"))

I need the formula to max value to corespondent range.
If A1=02:00:00 AM, then the range for max values to be B1:B200
If A1=04:00:00 AM, then the range for max values to be B3:B200
....etc

It would really help to change only the value in A1 and the formula to work with the new range, according to the A1 criteria.
I only need to change the starting row of the range( B1, B2,...etc.

Can this be done in just one formula? I am trying to avoid using too many formulas to retrieve cell and column numbers.

Thank you in advance,
Stef.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
is the Row always going to be 1 less than the Hour in Sheet2 A1

So if its 3pm
would that be row 14 ?

you could use a reference for the address row as
(A1*24)-1

=MAX(INDIRECT("'"&B1&"'!"&ADDRESS(((A1*24)-1),2)&":B200"))

Book2
ABCDEFG
119:00Sheet1
2Result(A1*24)-1
310-518
49
58
67
76
85
94
103
112
121
130
14-1
15-2
16-3
17-4
18-5
19-6
20-7
21-8
Sheet1
Cell Formulas
RangeFormula
E3E3=MAX(INDIRECT("'"&B1&"'!"&ADDRESS(((A1*24)-1),2)&":B200"))
G3G3=(A1*24)-1

Hi Etaf,
Thank you foer the reply.

No, the "hour" in the A column is like this:
 2:00:00 AM 2:01:00 AM 2:02:00 AM 2:03:00 AM 2:04:00 AM 2:05:00 AM

dont understand
Sheet2
A1= 04:00:00 AM
I need the formula to max value to corespondent range.
If A1=02:00:00 AM, then the range for max values to be B1:B200
If A1=04:00:00 AM, then the range for max values to be B3:B200
....etc

Can you now give examples of what you need
I dont understand the reference to column A
As you only mentioned A1

Book1
AB
12:00:00 AM1.10%
22:01:00 AM3.20%
32:02:00 AM8.50%
42:03:00 AM9.10%
52:04:00 AM18.23%
62:05:00 AM2.50%
72:06:00 AM4.80%
82:07:00 AM6.30%
92:08:00 AM6.20%
102:09:00 AM9.20%
112:10:00 AM5.50%
122:11:00 AM4.60%
132:12:00 AM7.50%
142:13:00 AM11.20%
152:14:00 AM2.11%
162:15:00 AM9.33%
172:16:00 AM3.87%
182:17:00 AM7.80%
192:18:00 AM4.50%
202:19:00 AM2.30%
212:20:00 AM1.60%
222:21:00 AM1.90%
Sheet1

And this is sheet 2:
Book1
ABC
12:10:00 AMSheet111%
2
3
4
5
6
7
8
Sheet2
Cell Formulas
RangeFormula
C1C1=MAX(INDIRECT("'"&B1&"'!"&"B11:B200"))

Thanks.

OK, so still not understanding

Why is the range NOW B11:B200 ?????
Sheet2!A1 02:10:00

Sheet2 A1 has 2:10:00
So that means the range should be B1:B200

If it was 4:55:00
then would it B3;B200

I'm missing something here , as your original post

Sheet2
A1= 04:00:00 AM
B1=Sheet name (Sheet1)
C1= Max formula (=MAX(INDIRECT("'"&B1&"'!"&"B3:B200"))

I need the formula to max value to corespondent range.
If A1=02:00:00 AM, then the range for max values to be B1:B200
If A1=04:00:00 AM, then the range for max values to be B3:B200
....etc

which is all sheet2

OK, so still not understanding

Why is the range NOW B11:B200 ?????
Sheet2!A1 02:10:00

Sheet2 A1 has 2:10:00
So that means the range should be B1:B200

If it was 4:55:00
then would it B3;B200

I'm missing something here , as your original post

which is all sheet2
Ok, so... I am not good at explaining, but:
1. At first A1:A200 had a full hour as an example(2:00:00 AM, 2:00:00 AM,...etc). Then you asked: is the Row always going to be 1 less than the Hour in Sheet2 A1? And I said no because your formula was tailored for a full hour, and in my table, the time was by the minute2:00:00 AM, 2:01:00 AM,...etc). It was my mistake, I should have used the time as it was on my real table. Sorry about that.
So my last post is the correct one.
2. Why is the range NOW B11:B200 ????? Sheet2!A1 02:10:00 AM
Well, the range is B11:B200 because 02:10:00 starts at row 11.
if in Sheet2!A1 I have 02:10:00 AM, then the max value range to start from B11 (B11:B200).
if in Sheet2!A1 I have 02:11:00 AM, then the max value range to start from B12 (B12:B200).
if in Sheet2!A1 I have 02:12:00 AM, then the max value range to start from B13 (B13:B200).

Please take as an example my last mini table example.
Thanks.

Hi, please look at the Sheet2 below. Sheet 1 remains the same. I need the same result with just one formula if possible.
Book1
ABCDE
12:10:00 AMSheet111%Range startRange end
21122
3
4
5
6
7
8
Sheet2
Cell Formulas
RangeFormula
C1C1=MAX(INDIRECT("'"&B1&"'!"&"b"&D2&":b"&E2))
D2D2=MATCH(A1,Sheet1!A1:A22,0)+ROW(Sheet1!A1:A22)-1
E2E2=MATCH(9.99999999999999E+307,Sheet1!A1:A22)

Thanks for your help.

so you are looking up the value in Sheet2!A1 to find the row that time exists in sheet1!A2:A10000

or however many rows - not sure how many you are using

so does Sheet1! A2:A100000 - have all the possible times in a 24hour clock every minute
so 1440 entries
00:00:00 to 23:59:00

BUT you are using in your example upto row 200 ???

BUT As with time, matching can be an issue because of the way Excel uses time

=MAX(INDIRECT("'"&B1&"'!"&ADDRESS(MATCH(ROUND(Sheet2!A1,15),ROUND(A1:A200,15),0),2)&":B200"))

I have on sheet2 02:10:00

BUT I'm not sure this is going to work for you and there maybe a lot of exceptions

Book2
ABCDEFG
101:59:00Sheet1
202:00:00100ResultINT((A1*24)-1)
302:01:009990-1
402:02:0098
502:03:0097ROW
602:04:009612
702:05:0095
802:06:0094
902:07:0093
1002:08:0092
1102:09:0091
1202:10:0090
1302:11:0089
1402:12:0088
1502:13:0087
1602:14:0086
1702:15:0085
1802:16:0084
1902:17:0083
2002:18:0082
2102:19:0081
2202:20:0080
2302:21:0079
2402:22:0078
2502:23:0077
2602:24:0076
2702:25:0075
2802:26:0074
2902:27:0073
Sheet1
Cell Formulas
RangeFormula
E3E3=MAX(INDIRECT("'"&B1&"'!"&ADDRESS(MATCH(ROUND(Sheet2!A1,15),ROUND(A1:A100,15),0),2)&":B200"))
G3G3=INT((D1*24)-1)
G6G6=MATCH(ROUND(Sheet2!A1,15),ROUND(A1:A100,15),0)

Replies
1
Views
604
Replies
6
Views
212
Replies
6
Views
92
Replies
3
Views
175
Replies
5
Views
164

Threads
1,203,751
Messages
6,057,147
Members
444,908
Latest member
Jayrey

### 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

### 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