Simple Hyperlink problem that has me baffled

I have a simple Hyperlink problem but have gone down soooo many Google Search 'Bunny Trails' without finding the exact solution trying various MATCH, etc. formula examples ;-(

In my single page spreadsheet (using Excel Starter 2010), I have:

LIST #1 - Multi-Column Data

LIST #2 - Multi-Column (Reference) Data which contains *some* of the LIST #1 data

Example of the quest:

In LIST #1, Column A, I have the (example) number 500 in a data cell. There is a matching 'number 500' in a data cell contained in LIST #2 data which is positioned BELOW List #1 starting on Excel ROW 100.

What I need to do is create a Hyperlink that when I click on the (example) number 500 in LIST #1, takes me to the line/row in LIST #2 with (example) number 500 - that also contains some additional data to be researched.

The DATA#'s in LIST #1 themselves need to be the clickable Hyperlinks.

NOTE:

1. I do NOT want to "LOOKUP" and/or "REPLACE" the contents in any LIST #1 or LIST #2 data cells !!!

2. Whatever the =HYPERLINK (blah blah) formula is the 'Magic Bullet', I need to create ALL of the LIST #1 Column A data cell Hyperlinks (which have different numbers) *separately* with a script outside of EXCEL that will be included in the importing (Opening) of a simple.txt file. And, for only the DATA# to display in the cell (the DATA#'s in LIST #1 themselves need to be the clickable Hyperlinks). Any references cannot be made to a specific Excel row number, only the cell DATA# (except there would be a Range of rows/cells for the LIST #2 data to be searched).

In a separate column, I'm doing something similar now with a simple URL to access WEB data like: http://www.exampleonly.com/db/DATA#. BUT... the entire URL displays, and does NOT work until I double-click on it a 2nd time and it turns into an underlined Hyperlink. Maybe it's because I'm using Excel Starter 2010, but I have not been able to find a way to reformat these 'Globally' so that ONLY the *short* different "DATA#" numbers appear in the cell. Editing/reformatting each entry manually via the Edit Hyperlink dialog box is not a workable option (hence, the frustration).

Any assistance is greatly appreciated!

Thank you.

- excelcrooner

Not sure I understand but maybe this does what you want.

Data Range

	A	B
1	78	Go
2	90	Go
3	71	Go
4	68	Go
5	61	Go
6
7	------	------
8
9
10	90
11	71
12	61
13	68
14	78

This array formula** entered in B1:

=HYPERLINK("#A"&MAX((A$10:A$14=A1)*ROW(A$10:A$14)),"Go")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

So, where is the data in List1 coming from, in the 1st place? It sounds like it gets replaced/updated? If so, then there is no regular formula that will do this for you - it will get over-written each time

Another option that comes to mind would be to have a 2nd sheet with the H/L, linking back to the 1st sheet - sort of a helper sheet.

Having said all that, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Originally Posted by FDibbins

So, where is the data in List1 coming from, in the 1st place? It sounds like it gets replaced/updated? If so, then there is no regular formula that will do this for you - it will get over-written each time

Another option that comes to mind would be to have a 2nd sheet with the H/L, linking back to the 1st sheet - sort of a helper sheet.

Hi, Ford:

Thanks for your post & the instructions for uploading. Along with the small (sample) file, I decided to also post an image here.

The data is from a *.txt file produced by a complicated Perl script and is a synthesized "ERROR LOG" requiring research in another *.txt file that contains the "MASTER LOG" for reference. Whatever Excel formulas are needed in the column can be auto-generated in the *.txt file data output. The objective is to eliminate/reduce "Manual" labor and get right to the Error Investigation tasks!

Yes, a 2nd worksheet is actually preferable - or even to link to a separate file would work. Linking - "jumping" to the desired number lines in the original "MASTER LOG" *.txt file would work. If the volume of data increases substantially, then everything will likely be migrated to a MySQL database but that's premature at this point.

The Error Matrix itself has less than 100 entries but could double or triple sooner than later in the next use. The Master Log has a tad under 2,000 entries which could also double or triple.

I've only included the first 2 columns in the sample file and a example of a URL Hyperlink from one of the far right columns in the actual spreadsheet (which fills up a 23" 1020x1080 screen).
That http entry is what I also need to display as only 749 in the column (and when clicked on, is actually the full URL action).

Regarding the QID & QWIDS columns.

Whether having LIST #2 on the same worksheet page, a different page, external Excel file or even the Master Log *.txt file, I'll reiterate from the original post that need is to have ONLY the actual data numbers display in LIST #1, BUT... be clickable Hyperlinks.

So when ANY number in the LIST #1 QID column is clicked on, it will jump to the that *same* number (and corresponding row) in the LIST #2 file. There will *always* be an identical number in the LIST #2 QID column.

When any number (whether solo or to the left or right of the | (pipe) symbol) in the LIST #1 QWID column is clicked on (it is actually a QID number), it will jump to that *same* number (and corresponding row) in the LIST #2 QID column.

I hope that better explains things. Now to attempt uploading the small (sample) file and the image.

Thanks!

- excelcrooner (a strange name, I know ... guess I was thinking of Bing Crosby at the time :^)

Sorry, I'm lost!

Originally Posted by Tony Valko

Sorry, I'm lost!

Hi, Tony:

I'll try to explain this a bit better (hopefully).

The desired end goal is to click on a number in the QID or QWIDS column(s) of LIST #1 and jump to to that *same* number in the QID line/row in LIST #2 (whether this data is on the same worksheet/page, a separate page, a separate Excel file, *.txt file, or this *could* even be a WEB located document). BUT (and the real biggie here):

1. ONLY the QID or QWIDS numbers can display in the LIST #1 columns as "clickable" numbers themselves.

2. I must be able to first (auto) pre-configure whatever formulas are required as LIST #1 QID or QWIDS column data in a standalone file that gets imported or opened in the Excel page with the (workable) forumlas intact for each separate QID or QWIDS numbers and be ready for use.

So if I click on the data number 500 in the QID column of LIST #1 (the "Error Log Matrix"), this results in a "jump" to the same data number 500 in the QID column of LIST #2 where I can then analyze and evaluate ALL the data columns on *that* entire row in what is the "Master Log File".

Does this help?

Thanks.

- excelcrooner

Originally Posted by excelcrooner

The other LIST #1 to LIST #2 Hyperlinking solution remains a Mystery

I don't know how to do what you're describing.

You may need to do this with VBA code.

Good luck!