 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Help with formula
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!))) Modify what? > Hi All: > [quoted text clipped - 11 lines] > > =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!))) We need to see the original formula. > Modify what? > [quoted text clipped - 13 lines] >> >> =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!))) Hi Tyro I copied and pasted the formula to the bottom of this message below my name "Keith". Thank You!!!! >We need to see the original formula. > [quoted text clipped - 15 lines] >>> >>> =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!))) That formula has errors in it. > Hi Tyro > [quoted text clipped - 22 lines] >>>> >>>> =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!))) Think it would help to see the formula before you copy it. Regards Trevor > Hi All: > [quoted text clipped - 11 lines] > > =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!))) 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 > Hi All: > [quoted text clipped - 11 lines] > > =IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&CEL?L("address",INDIRECT("H"&MATCH(#REF!,$H$31:$H$65536,0)+30)),#REF!))) 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 >You have posted a formula with errors in it. > [quoted text clipped - 26 lines] >> >> =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!))) Show us the original formula before you get the #REF error. Why is it so difficult for you to understand? > Pete: > I have cut the data from sheet 1 and pasted it to sheet 2. When I [quoted text clipped - 35 lines] >>> >>> =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!))) 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))) >Show us the original formula before you get the #REF error. Why is it so >difficult for you to understand? [quoted text clipped - 38 lines] >>>> >>>> =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!))) >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.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- ------ 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Have a nice day!" <invalid@email.none> wrote in message news:490ke3t6vduqqs8a218un87uv2d51lrhif@4ax.com... >I am trying to build a index for lack of a better name. I would like > to build a list several of the itmes "cells" at the beginning of a [quoted text clipped - 12 lines] > > John Much Thanks Max!!!!!!!!!!! I will definately let you know if I can make this work, and let you know!!!!!! Thanks again Keith >>I tried my hand at typing this from the spreadsheet. It would'nt let >> me copy paste. [quoted text clipped - 8 lines] > >Don't retype the long formula, you're likely to introduce errors. 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. >>I tried my hand at typing this from the spreadsheet. It would'nt let >> me copy paste. [quoted text clipped - 8 lines] > >Don't retype the long formula, you're likely to introduce errors. 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 SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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: 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= >Welcome, but I'm sorry that you had difficulty adapting it to suit > [quoted text clipped - 4 lines] >http://www.flypicture.com/ >http://cjoint.com/index.php 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) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Max: > FINALLY something I can do!!! I want to move everything in rows 1 [quoted text clipped - 8 lines] > > http://www.flypicture.com/download/NDE3NDU= 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 >In Sheet2, > [quoted text clipped - 16 lines] >http://www.flypicture.com/download/NDE5NjI= >(price_list_with_hyperlinks_1.xls) > .. It works beautifully and exactly as I needed... Ahh, always good to hear that. You're welcome, Keith. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Max: > I cant thank you enough for your help on this!!!! It works [quoted text clipped - 6 lines] > > Keith
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.