Range row based on criteria

hstef · Jan 9, 2022

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.

etaf · Jan 9, 2022

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

A

B

C

D

E

F

G

1

19:00

Sheet1

2

Result

(A1*24)-1

3

10

-5

18

4

9

5

8

6

7

6

8

5

9

4

10

3

11

2

12

1

13

0

14

-1

15

-2

16

-3

17

-4

18

-5

19

-6

20

-7

21

-8

Sheet1

Cell Formulas
Range	Formula
E3	E3	=MAX(INDIRECT("'"&B1&"'!"&ADDRESS(((A1*24)-1),2)&":B200"))
G3	G3	=(A1*24)-1

hstef · Jan 9, 2022

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

etaf · Jan 9, 2022

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

hstef · Jan 9, 2022

Book1

A

B

1

2:00:00 AM

1.10%

2

2:01:00 AM

3.20%

3

2:02:00 AM

8.50%

4

2:03:00 AM

9.10%

5

2:04:00 AM

18.23%

6

2:05:00 AM

2.50%

7

2:06:00 AM

4.80%

8

2:07:00 AM

6.30%

9

2:08:00 AM

6.20%

10

2:09:00 AM

9.20%

11

2:10:00 AM

5.50%

12

2:11:00 AM

4.60%

13

2:12:00 AM

7.50%

14

2:13:00 AM

11.20%

15

2:14:00 AM

2.11%

16

2:15:00 AM

9.33%

17

2:16:00 AM

3.87%

18

2:17:00 AM

7.80%

19

2:18:00 AM

4.50%

20

2:19:00 AM

2.30%

21

2:20:00 AM

1.60%

22

2:21:00 AM

1.90%

Sheet1

hstef · Jan 9, 2022

And this is sheet 2:

Book1

A

B

C

1

2:10:00 AM

Sheet1

11%

2

3

4

5

6

7

8

Sheet2

Cell Formulas
Range	Formula
C1	C1	=MAX(INDIRECT("'"&B1&"'!"&"B11:B200"))

Thanks.

etaf · Jan 9, 2022

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

hstef · Jan 9, 2022

etaf said:
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 minute

2: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.

hstef · Jan 9, 2022

Hi, please look at the Sheet2 below. Sheet 1 remains the same. I need the same result with just one formula if possible.

Book1

A

B

C

D

E

1

2:10:00 AM

Sheet1

11%

Range start

Range end

2

11

22

3

4

5

6

7

8

Sheet2

Cell Formulas
Range	Formula
C1	C1	=MAX(INDIRECT("'"&B1&"'!"&"b"&D2&":b"&E2))
D2	D2	=MATCH(A1,Sheet1!A1:A22,0)+ROW(Sheet1!A1:A22)-1
E2	E2	=MATCH(9.99999999999999E+307,Sheet1!A1:A22)

Thanks for your help.

etaf · Jan 9, 2022

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

A

B

C

D

E

F

G

1

01:59:00

Sheet1

2

02:00:00

100

Result

INT((A1*24)-1)

3

02:01:00

99

90

-1

4