Help with formula

[Have a nice day!]

Hi All:

I am WAY over my head here, and you all have always been great help,
so hopefully someone can help with this. I had someone write this for
me to use as an index at the top of a spread sheet. It works
beautifully . The problem is I would like to move this index to sheet
2 of the same spreadsheet and when I copy and paste it, it does'nt
work. Is there anyone who can modify this to work as an index in
sheet 2 and will search sheet 1 for the results?

TIA

Keith


=IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("H"&MATCH(#REF!,$H$31:$H$65536,0)+30)),#REF!)))

 

[Tyro]

Modify what?

 

[Tyro]

We need to see the original formula.

Modify what?

 

[Trevor Shuttleworth]

Think it would help to see the formula before you copy it.

Regards

Trevor

 

[Have a nice day!]

Hi Tyro

I copied and pasted the formula to the bottom of this message below my
name "Keith".

Thank You!!!!

 

[Tyro]

That formula has errors in it.

Hi Tyro

I copied and pasted the formula to the bottom of this message below my
name "Keith".

Thank You!!!!

 

[Pete_UK]

You have posted a formula with errors in it.

The thing to do is to start with a file which has a working formula,
insert a new worksheet, then CUT the formula and PASTE into the new
sheet, so that you are moving it rather than copying it.

If you still want the original formula in the first sheet, then use
File | Save As to give this modified file a different name, then open
the original file and COPY the formula from that file to the first
sheet in the new file.

Hope this helps.

Pete

 

[Have a nice day!]

Pete:
I have cut the data from sheet 1 and pasted it to sheet 2. When I
click on a particular cell on sheet 2 (the data on sheet 2 is used
only as a index) it takes me to the exact row and coulmn on sheet 2
that the data I'm requested is listed in on sheet 1 instead of taking
me to sheet 1. Your help is appreciated!!!

Keith

 

[Tyro]

Show us the original formula before you get the #REF error. Why is it so
difficult for you to understand?

 

[Have a nice day!]

I tried my hand at typing this from the spreadsheet. It would'nt let
me copy paste. Many Thanks


=IF(A2="","",IF(INSA(MATCH(A2,B$20:B$200,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("B"&MATCH(A2,B$20:B$200,0)+19)),A2)))

 

[Max]

I tried my hand at typing this from the spreadsheet. It would'nt let
me copy paste.

Believe you are referring to my earlier response to your moniker about a
month ago (attached below for easy reference). I received no feedback to
that response.

In the sample file given, you could always use the arrow keys to select the
cell, then select & copy the formula directly from the formula bar, and
paste directly into another cell's formula bar.

Don't retype the long formula, you're likely to introduce errors.

---
------ previous response --------------------
One play which delivers this using a single click hyperlink ..

Illustrated in this sample:
http://www.savefile.com/files/1054222
Hyperlink item to table range below.xls

Assuming items listed in A2:A10,
reference table range in rows 20 to 200 with items list in B2:B200

In B2:
=IF(A2="","",IF(ISNA(MATCH(A2,B$20:B$200,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("B"&MATCH(A2,B$20:B$200,0)+19)),A2)))
Copy down to B10. Hide away col A. The above creates hyperlinks in col B
which jumps to the correct row in the range below (B2:B200) which matches
with the item shown in the hyperlink.

---

 

[Have a nice day!]

Much Thanks Max!!!!!!!!!!!
I will definately let you know if I can make this work, and let you
know!!!!!!

Thanks again
Keith

 

[Have a nice day!]

Max:
I appreciate your help, but I do not have a good basic knowlegde of
this code. I tried your formula and can't get it to work. Thanks for
all your help. Both past and present.

 

[Max]

Welcome, but I'm sorry that you had difficulty adapting it to suit

Perhaps you might consider using either of the 2 free filehosts below to
upload a sample of your actual file (Desensitize it, if needed). Then copy &
paste the generated link to your sample file in response here:

http://www.flypicture.com/
http://cjoint.com/index.php

 

[Have a nice day!]

Max:
FINALLY something I can do!!! I want to move everything in rows 1
thru 30 to sheet 2. I cut and pasted all the data from row 1 thru 30
to sheet 2 but it did not search correctly. I know this is probably
simple to you guys, but difficult for someone who is excel
challenged!!!!

You are a kind and patient guy and I appreciate you and everyones help

Thanks again

http://www.flypicture.com/download/NDE3NDU=

 

[Max]

In Sheet2,

I pasted the 1st 30 rows over from Sheet1

Then I adjusted the formula in G16 to:
=IF(G1="","",IF(ISNA(MATCH(G1,Sheet1!$H$31:$H$65536,0)),"",
HYPERLINK("#"&CELL("address",INDIRECT("Sheet1!H"&MATCH(G1,Sheet1!$H$31:$H$65536,0)+30)),G1)))

Copied G16 and pasted special as formulas over the range G16:N30

The "Sheet" reference within the INDIRECT (ie "Sheet1!H") is the only change
you need to make to the earlier formula, so that it now jumps you to Sheet1
(from the hyperlinks in Sheet2)

I also created a normal hyperlink in Sheet1's I31
titled: Return to Sheet2, for easy return to Sheet2

Here's the revised file:
http://www.flypicture.com/download/NDE5NjI=
(price_list_with_hyperlinks_1.xls)

 

[Have a nice day!]

Max:
I cant thank you enough for your help on this!!!! It works
beautifully and exactly as I needed. You are a patient and good
guy!!!!!!!!!!


Again


Thanks so Much!!!!!!!!!!!!!!!!!!!!!!!!!


Keith

 

[Max]

.. It works beautifully and exactly as I needed...
Ahh, always good to hear that. You're welcome, Keith.