I have two worksheets. One has a cell with a validation list followed by a number of cells containing a table of contents list. The other worksheet has 2-4 tables of hyperlinks, which corresponds with the table of contents on the first worksheet. The user can select the table they want to use from the cell with the validation list on the first worksheet. When the user does this I want the table of contents on the first worksheet to appear with the hyperlinks for the table they selected.
For example:
Worksheet #2
(ie Column X: Row 1 (Table X), Row 2 (hyperlink 1), Row 3(hyperlink 2), Row 4(hyperlink 3),etc)
Column A: Table A, 1a, 2a, 3a, 4a,…
Column B: Table B, 1b, 2b, 3b, 4b,…
Column C: Table C, 1c, 2c, 3c, 4c,…
Worksheet #1
When the validation list cell value = Table B
then in Column 1 “Table B” hyperlinks 1b, 2b, 3b, 4b,…. will be displayed.
Right now the only way I have found this to work is by using one very large formula with multiply IF statements. Doing so makes the second worksheet useless in the fact that I need to manually copy all of the hyperlinks and put them in the IF statements on the worksheet 1. For example in the Table of contents cells on worksheet 1 I have used the following:
=IF(D3=”Table A”,HYPERLINK(“//www.programs.com/worksheet.xls#worksheet!A1″,”Section 1″),IF(D3=”Table B”,HYPERLINK(“//www.programs.com/worksheet.xls#worksheet!B1″,”Section 1″),””))
This really is complex, I hope I explained it clearly enough.