F
Fred
I have created a sort of Table of Contents of project names held
within row 3 of a worksheet. This I achieved by modifying an entry on
www.contextures.com (FN0006 - Extract List of Unique Items -- Use
formulas to extract unique items from a list. Based on an example from
Paul Cumbers), outputting the result using
=IF(MAX(NameCount)<ROW(1:1),"",HLOOKUP(ROW(1:1),NameList,2))
..
..
..
=IF(MAX(NameCount)<ROW(22:22),"",HLOOKUP(ROW(22:22),NameList,2))
NameCount is defined as "=OFFSET(Demand!$Q$2,0,0,1,239)"
NameList is defined as "=OFFSET(Demand!$Q$2,0,0,2,239)"
UniqueNames is defined as"=OFFSET(Demand!$M$4,0,0,MAX(Demand!$Q2:$IV2),
1)" but is not required within my worksheet.
Row 3 contains Project Names, "Status", a variety of Status settings
(as defined in StatusTab) and blank cells
Row 2 contains the following formula
=IF(R3<>"Status",IF(ISNA(VLOOKUP(R3,StatusTab,
2,FALSE)),IF(COUNTIF($Q3:R3,R3)=1,MAX($Q2:Q2)+1,""),""),"")
This provides me with the list of projects and ignores blank cells or
cells with data I'm not interested in.
What I would now like to do, when a user clicks on one of those cells
in the ToC, is take them to the location of the selected project in
the worksheet. I've looked at Hyperlinks and the =HYPERLINK, and
tried wrapping it into the above formula, but cannot get it to take me
to the appropriate cell on the worksheet.
Any and all suggestions gratefully received.
Regards
Fred
within row 3 of a worksheet. This I achieved by modifying an entry on
www.contextures.com (FN0006 - Extract List of Unique Items -- Use
formulas to extract unique items from a list. Based on an example from
Paul Cumbers), outputting the result using
=IF(MAX(NameCount)<ROW(1:1),"",HLOOKUP(ROW(1:1),NameList,2))
..
..
..
=IF(MAX(NameCount)<ROW(22:22),"",HLOOKUP(ROW(22:22),NameList,2))
NameCount is defined as "=OFFSET(Demand!$Q$2,0,0,1,239)"
NameList is defined as "=OFFSET(Demand!$Q$2,0,0,2,239)"
UniqueNames is defined as"=OFFSET(Demand!$M$4,0,0,MAX(Demand!$Q2:$IV2),
1)" but is not required within my worksheet.
Row 3 contains Project Names, "Status", a variety of Status settings
(as defined in StatusTab) and blank cells
Row 2 contains the following formula
=IF(R3<>"Status",IF(ISNA(VLOOKUP(R3,StatusTab,
2,FALSE)),IF(COUNTIF($Q3:R3,R3)=1,MAX($Q2:Q2)+1,""),""),"")
This provides me with the list of projects and ignores blank cells or
cells with data I'm not interested in.
What I would now like to do, when a user clicks on one of those cells
in the ToC, is take them to the location of the selected project in
the worksheet. I've looked at Hyperlinks and the =HYPERLINK, and
tried wrapping it into the above formula, but cannot get it to take me
to the appropriate cell on the worksheet.
Any and all suggestions gratefully received.
Regards
Fred