Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
Use of hyperlink formula's can allow much movement around a sheet, some used
in header would move down to max or min values in a sheet will submit example at end of this post. the following works for use "in-document" to move progressively down a column, to: in this example, the next occurrance of "dn" (generated by this formula). $Z$3 is the fixed cell where reference to how many rows I have showing in my sheet, also included. use cntrl-shift-enter the formula, subsequent copy-pastes will merely work. "DOWN" formula is: hi, think have found what would be 90% the answer: (while combining old formula's), found that deleting item for: EXACT, could get formula to work. If there is an answer for use of exact, will still take that response. Formula works as: =HYPERLINK(IF(ISNA( INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)), MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0)))," ", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AT183),"$",""),ROW(),"")& INDEX(ROW(OFFSET($AT183:$AT$1156,1,0)), MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0))),$ Z$3,0))),"dn") note: $Z$3 is a work cell for using amount of rows avail. minus number of rows in "Freeze Pane" header, such as: =ROW($A$51)-ROW($A$17) you can further externalize the value "For" $A$51 (e.g. in $AQ$3): =$AQ$3-CELL("row",$A$17) step further: externalize number of rows you might hide / collapse in header: ($Y$3-CELL("row",$A$17)) & y3: =$AQ$3+$AT$3 (have fun figuring that out). XXXXXXXXXXXX use: cntrl-shift-enter for array formula's MAX Hyperlink example, I use in header / freeze pane of document: =HYPERLINK(IF(ISNA(INDEX(ROW(CS$175:CS$723)-ROW(CS$175), INDEX(ROW(CS$175:CS$723),MATCH(MAX(CS$175:CS$723), CS$175:CS$723,0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",CS12),"",""),ROW(),"")& INDEX(ROW(CS$175:CS$723),MATCH(MAX(CS$175:CS$723), CS$175:CS$723,0))),$Z$3,0))),MAX(IF(ISNUMBER(CS$17 5:CS$723),CS$175:CS$723,-1E+100))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
Example of: find a (error response) character in a column, use of a
"dead-zone" area, show quantity of "X" 's =HYPERLINK(IF(AND( ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))), ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AV13),"$",""),ROW(),"")&IND EX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$175: $BU$1156,"X"),0))),$Z$3,0))), IF($DD$14<"0 top",IF(AND( ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))), ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"", INDEX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$1 75:$BU$1156,"X"),0))),"delist'd")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
I wouldn't call it neat in the true sense of the word
<bg -- Regards, Peo Sjoblom "nastech" wrote in message ... Example of: find a (error response) character in a column, use of a "dead-zone" area, show quantity of "X" 's =HYPERLINK(IF(AND( ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))), ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AV13),"$",""),ROW(),"")&IND EX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$175: $BU$1156,"X"),0))),$Z$3,0))), IF($DD$14<"0 top",IF(AND( ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))), ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"", INDEX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$1 75:$BU$1156,"X"),0))),"delist'd")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
recommend destination cell (after hyperlink jump) is to an empty column
(e.g.: B) so if have bad mouse, / bad click, do not accidently paste over data. use in all examples, this example (in-doc move down sheet) items changed: address destination either $A, with 0 after $Z$3, changed to: 1 or use column $B instead of $A.. =HYPERLINK(IF(ISNA( INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)), MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0)))," ", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A183),"$",""),ROW(),"")& INDEX(ROW(OFFSET($AT183:$AT$1156,1,0)), MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0))),$ Z$3,1))),"dn") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
ouch :) , correction here,, with a 1 after $Z$3
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Retrieving info from "child" to a "parent" document | Excel Discussion (Misc queries) | |||
Answer to HYPERLINK "In-Document" | Excel Discussion (Misc queries) | |||
Solution "Your changes could not be saved" "The document may be read-only or encrypted" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |