Thread: Hyperlinks help

Hi I first any help you can offer for this problem will be greatly appreciated.

I have just started a new job and part of it involves the logging of 22100 samples, which are contained in 17 boxes, each split into 13 trays and each tray containing 100 samples. I inherited a spreadsheet which gave a list of all the samples. I set about trying to improve this and decided that I wanted to keep with using Excel. So I made a cover page with each of the 17 boxes represented by a letter and clickable hyperlinks to take you to an associated page within the spreadsheet for each letter. The page that this took you to then has the 13 trays represented by numbers and again clickable links to take you to the corresponding pages. So for example, if you click on A this would take you to page 'A' and then clicking on 5 would take you to page 'A5'. On the top of each page 'A1' to 'Q13' I have a 10 by 10 grid representing the 100 samples contained within, as there will not always be samples present I have set each cell in the 10 by 10 grid to go green if there is a sample present, I also have then made 1 set of the 10 by 10 grid clickable hyperlinked so that when you click the number it takes you to the information for what sample is contained within the cell which is contained below the grid, however when I try to paste this formula to the next page it makes still hyperlinks back to the original page meaning that it I pasted the formula from page 'A1' to page 'A5' and clicked on number 13 on page 'A5' the hyperlink would take me to the thirteenth sample on page 'A1', please can anyone help with how to fix this as I dont want ot go through and create 22100 individual hyperlinks??

Also I then have all the data link back to a search page so that all the sample info is contained in the one worksheet for searching purposes, and to do this I put in for example cell ='A1'!B15 however now when I click Find on this page and enter the name of a sample it is unable to find it as the cells display the sample info but do not seem to be searchable, again can anyone tell me where I have gone wrong??

Finally on my search page I have a column called location and again I want each individual entry to be a hyperlink to the relevent place on the relevent page but again I dont fancy doing this 22100 times and the data is spread accross 221 pages so please can anyone help me??

Thanks in advance for any help which anyone can offer

Keith

Hi
I could not follow the last part. 10 x 10 links are provided on the page for 100 samples. how are they organized ? Since you have used B15, I assume you have 20 rows and 5 columns.
Ravi

Hi
how about a solution as shown in the attached file
Ravi

Hi Thanks to everyone who took the time to read and reply to my problem I was having, it is greatly appreciated, sorry for the delay in replying I was off work for the last week and didn't check on this forum while I was off. To both of the forum users who sent me working examples I am eternally greatful and would like to say thank you very much as you have just saved me a whole load of hyperlinking


Thanks Keith