1. Trying to find the location of the maximum number, in a column (just row
#).
(some material working with is below)
2. Goal, if have to send result to separate cell?, is to be able to make a
hyperlink that will let me navigate quickly to item. (thanks), example:
=HYPERLINK($AA$5&V9,"h")
3. other: Desparately trying to figure way to have links "in document"
that:
"expand / contract" in location (for in-document link).
i.e.: right click cell, hyperlink, in document, cell reference: does not
move with reference lines, as lines move.. (causing major delays in work,
re-applying cell reference every few minutes is too crazy) IS there a
check-box somewhere, I am missing?????????????????????, thanks

have the following working with:
=MAX(IF(ISNUMBER(BQ125:BQ727),BQ125:BQ727,-1E+100))

reference row #'s, for upper lower limits (where reference lines are):
=CELL("row",A1099) (moves as expands / contracts)

used to ref other work (upper-lower limits) such as:
=IF($F$13="U",SUMPRODUCT(--(C125:C727="p3")),IF($F$13="L",SUMPRODUCT(--(C727:C1099="p3")),SUMPRODUCT(--(C125:C1099="p3"))))

-------------------
also of interest (not related here), column letter formula:
=SUBSTITUTE(SUBSTITUTE(CELL("address",A11),"$","") ,ROW(),"")

number of occurances:
=SUMPRODUCT(--(LEFT(AI125:AI1099,1)={"u","d"}))

number of occurances, different columns:
=SUMPRODUCT(--(LEFT(BA80:BA1099,1)="x"),--(E80:E1099<"x"))

Let's say we want to locate and link to the maximum value in column A:

In F1:
=MAX(A:A)
In F2:
=MATCH(F1,A:A)
In F3:
=ADDRESS(F2,1)
In F4:
=HYPERLINK("#"&CELL("address",INDIRECT(F3)),"go to the max")

F1 will have the maximum value
F2 will have the row number
F3 will have the full address of the cell
F4 will have the hyperlink to it.

You can, of course, combine these formulae to avoid using extra cells.
--
Gary's Student


"nastech" wrote:

1. Trying to find the location of the maximum number, in a column (just row
#).
(some material working with is below)
2. Goal, if have to send result to separate cell?, is to be able to make a
hyperlink that will let me navigate quickly to item. (thanks), example:
=HYPERLINK($AA$5&V9,"h")
3. other: Desparately trying to figure way to have links "in document"
that:
"expand / contract" in location (for in-document link).
i.e.: right click cell, hyperlink, in document, cell reference: does not
move with reference lines, as lines move.. (causing major delays in work,
re-applying cell reference every few minutes is too crazy) IS there a
check-box somewhere, I am missing?????????????????????, thanks

have the following working with:
=MAX(IF(ISNUMBER(BQ125:BQ727),BQ125:BQ727,-1E+100))

reference row #'s, for upper lower limits (where reference lines are):
=CELL("row",A1099) (moves as expands / contracts)

used to ref other work (upper-lower limits) such as:
=IF($F$13="U",SUMPRODUCT(--(C125:C727="p3")),IF($F$13="L",SUMPRODUCT(--(C727:C1099="p3")),SUMPRODUCT(--(C125:C1099="p3"))))

-------------------
also of interest (not related here), column letter formula:
=SUBSTITUTE(SUBSTITUTE(CELL("address",A11),"$","") ,ROW(),"")

number of occurances:
=SUMPRODUCT(--(LEFT(AI125:AI1099,1)={"u","d"}))

number of occurances, different columns:
=SUMPRODUCT(--(LEFT(BA80:BA1099,1)="x"),--(E80:E1099<"x"))

Hi, giving it a try, but sure am not quite apt with formula's to figure out..
just spend alot of time at it.. looks like I know what I'm doing :) can't
quite get, have so far:

In F1: =MAX(A:A) maximum value
In F2: =MATCH(F1,A:A) row number
In F3: =ADDRESS(F2,1) have full address of cell
In F4: =HYPERLINK("#"&CELL("address",INDIRECT(F3)),"goto max")
=HYPERLINK("#"&CELL("address",A954),"x") in document link
works
=MAX(IF(ISNUMBER(BQ125:BQ734),BQ125:BQ734,-1E+100)) working from

=HYPERLINK("#"&CELL("address",INDIRECT(F3)),MAX(A: A)) trying to get
hyperlink to display max
=HYPERLINK("#"&CELL("address",MAX(A:A)),MAX(A:A) not
=HYPERLINK("#"&CELL("address",MAX(A:A)),"x") not
=HYPERLINK("#"&CELL("address",MAX(IF(ISNUMBER(BQ12 5:BQ734),BQ125:BQ734,-1E+100))),"x") not

-------------------------------------------
"Gary''s Student" wrote:

Let's say we want to locate and link to the maximum value in column A:

In F1:
=MAX(A:A)
In F2:
=MATCH(F1,A:A)
In F3:
=ADDRESS(F2,1)
In F4:
=HYPERLINK("#"&CELL("address",INDIRECT(F3)),"go to the max")

F1 will have the maximum value
F2 will have the row number
F3 will have the full address of the cell
F4 will have the hyperlink to it.

You can, of course, combine these formulae to avoid using extra cells.
--
Gary's Student


"nastech" wrote:

1. Trying to find the location of the maximum number, in a column (just row
#).
(some material working with is below)
2. Goal, if have to send result to separate cell?, is to be able to make a
hyperlink that will let me navigate quickly to item. (thanks), example:
=HYPERLINK($AA$5&V9,"h")
3. other: Desparately trying to figure way to have links "in document"
that:
"expand / contract" in location (for in-document link).
i.e.: right click cell, hyperlink, in document, cell reference: does not
move with reference lines, as lines move.. (causing major delays in work,
re-applying cell reference every few minutes is too crazy) IS there a
check-box somewhere, I am missing?????????????????????, thanks

have the following working with:
=MAX(IF(ISNUMBER(BQ125:BQ727),BQ125:BQ727,-1E+100))

reference row #'s, for upper lower limits (where reference lines are):
=CELL("row",A1099) (moves as expands / contracts)

used to ref other work (upper-lower limits) such as:
=IF($F$13="U",SUMPRODUCT(--(C125:C727="p3")),IF($F$13="L",SUMPRODUCT(--(C727:C1099="p3")),SUMPRODUCT(--(C125:C1099="p3"))))

-------------------
also of interest (not related here), column letter formula:
=SUBSTITUTE(SUBSTITUTE(CELL("address",A11),"$","") ,ROW(),"")

number of occurances:
=SUMPRODUCT(--(LEFT(AI125:AI1099,1)={"u","d"}))

number of occurances, different columns:
=SUMPRODUCT(--(LEFT(BA80:BA1099,1)="x"),--(E80:E1099<"x"))

Hello, I tried to figure the problem out to put all in one cell / formula,
but could not, not sure how some of items are working.

If I am correct, this would also seem related to another problem I am
working on:
to have an "in-document" hyperlink to go to a specified cell (e.g. A300)
where want title line of A300 to be at top of page (if moving dowwards), by
something like A300+32. Is there a way to fix a hyperlink as such / is that
part of functions you listed? thanks.

"nastech" wrote:

Hi, giving it a try, but sure am not quite apt with formula's to figure out..
just spend alot of time at it.. looks like I know what I'm doing :) can't
quite get, have so far:

In F1: =MAX(A:A) maximum value
In F2: =MATCH(F1,A:A) row number
In F3: =ADDRESS(F2,1) have full address of cell
In F4: =HYPERLINK("#"&CELL("address",INDIRECT(F3)),"goto max")
=HYPERLINK("#"&CELL("address",A954),"x") in document link
works
=MAX(IF(ISNUMBER(BQ125:BQ734),BQ125:BQ734,-1E+100)) working from

=HYPERLINK("#"&CELL("address",INDIRECT(F3)),MAX(A: A)) trying to get
hyperlink to display max
=HYPERLINK("#"&CELL("address",MAX(A:A)),MAX(A:A) not
=HYPERLINK("#"&CELL("address",MAX(A:A)),"x") not
=HYPERLINK("#"&CELL("address",MAX(IF(ISNUMBER(BQ12 5:BQ734),BQ125:BQ734,-1E+100))),"x") not

-------------------------------------------
"Gary''s Student" wrote:

Let's say we want to locate and link to the maximum value in column A:

In F1:
=MAX(A:A)
In F2:
=MATCH(F1,A:A)
In F3:
=ADDRESS(F2,1)
In F4:
=HYPERLINK("#"&CELL("address",INDIRECT(F3)),"go to the max")

F1 will have the maximum value
F2 will have the row number
F3 will have the full address of the cell
F4 will have the hyperlink to it.

You can, of course, combine these formulae to avoid using extra cells.
--
Gary's Student


"nastech" wrote:

1. Trying to find the location of the maximum number, in a column (just row
#).
(some material working with is below)
2. Goal, if have to send result to separate cell?, is to be able to make a
hyperlink that will let me navigate quickly to item. (thanks), example:
=HYPERLINK($AA$5&V9,"h")
3. other: Desparately trying to figure way to have links "in document"
that:
"expand / contract" in location (for in-document link).
i.e.: right click cell, hyperlink, in document, cell reference: does not
move with reference lines, as lines move.. (causing major delays in work,
re-applying cell reference every few minutes is too crazy) IS there a
check-box somewhere, I am missing?????????????????????, thanks

have the following working with:
=MAX(IF(ISNUMBER(BQ125:BQ727),BQ125:BQ727,-1E+100))

reference row #'s, for upper lower limits (where reference lines are):
=CELL("row",A1099) (moves as expands / contracts)

used to ref other work (upper-lower limits) such as:
=IF($F$13="U",SUMPRODUCT(--(C125:C727="p3")),IF($F$13="L",SUMPRODUCT(--(C727:C1099="p3")),SUMPRODUCT(--(C125:C1099="p3"))))

-------------------
also of interest (not related here), column letter formula:
=SUBSTITUTE(SUBSTITUTE(CELL("address",A11),"$","") ,ROW(),"")

number of occurances:
=SUMPRODUCT(--(LEFT(AI125:AI1099,1)={"u","d"}))

number of occurances, different columns:
=SUMPRODUCT(--(LEFT(BA80:BA1099,1)="x"),--(E80:E1099<"x"))