+ Reply to Thread
Results 1 to 36 of 36

Create HypherLink based on Formula Link of Worksheets in Same Workbook

  1. #1
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Post Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi,

    Seeking someone help to create VBA Code.

    I have one summary data where i have linked formula of other Sheets.

    Column C I am having Formuala Linked numbers within workbook of other Sheets
    Column D required Hypherlink of formula linked in column C (Same Row)
    Similarly on Source Sheet to Summary Sheet required Hypher link at defualt F1



    Note
    Column C need as active column as its not fixed one
    Column D also not fixed one it should be next of Active Column
    Column B Sheet Names are not fixed one will varry based on the sheets available in the workbook
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    If you mean something like this....

    EXCEL_2020-05-22_16-34-25.png

    The first condition of your Summary sheet is something like the image above (except column E, this is only for explanation).
    There is a formula in column C (the contains of the formula is as seen in column E), and how many rows which has value is not fixed.

    What you want is the automation where :
    1. cell B5 will have a value "Sheet2" (as seen in the column E)
    2. cell B6 will have a value "Sheet3" (as seen in the column E)
    3. cell B7 will have a value "Sheet4" (as seen in the column E)
    and so on

    4. cell D5 will have a link, where if the link is clicked it will go to Sheet2 cell D4
    5. cell D6 will have a link, where if the link is clicked it will go to Sheet3 cell D13
    6. cell D7 will have a link, where if the link is clicked it will go to Sheet4 cell F8
    and so on


    So, it looks like something like this after the automation :
    2020-05-22_16-36-04.gif

    And also on each "source" sheet cell F1, you want a link to link back to Sheet Summary.

    If that's what you mean, I do my way something like this :
    Please Login or Register  to view this content.
    Last edited by karmapala; 05-22-2020 at 05:14 AM.

  3. #3
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    Thanks for your code.

    I am getting Hypherlink in Summary Sheet but after completion of Macro i am getting the below Error

    Attachment 678950

    Similarly on Source Sheet to Summary Sheet required Hypher link on defualt F1
    I mean the source workSheets i need Hypherlink at default Cell F1. This Hypherlink cell should got to the summary sheet on respective Cell
    Attached Images Attached Images

  4. #4
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Click Debug and please tell me at what line it's yellow highlighted. Because on my side, the code doesn't give me an error.

    imilarly on Source Sheet to Summary Sheet required Hypher link on defualt F1
    I mean the source workSheets i need Hypherlink at default Cell F1. This Hypherlink cell should got to the summary sheet on respective Cell
    Yes, I already add the code for "source" sheet cell F1.


    Have you removed your notes :
    Column C I am having Formula Linked numbers within workbook of other Sheets
    Column D required Hypherlink of formula linked in column C (Same Row)
    Similarly on Source Sheet to Summary Sheet required Hypher link on defualt F1



    Note
    Column C need as active column as its not fixed one
    Column D also not fixed one it should be next of Active Column
    Column B Sheet Names are not fixed one will varry based on the sheets available in the workbook
    in sheet summary columns C ?
    If you haven't clear it, then the code will throw you an error.
    Last edited by karmapala; 05-22-2020 at 05:25 AM.

  5. #5
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Getting error at this row

    Please Login or Register  to view this content.
    And Column B is for just reference i have added, if i remove column B text, not getting Hypher link.

    Expecting to get Hypher link based on Column C reference Ex "Sheet1!$G$11"

    And Hypher link Text To Display i am requesting Worksheet Name + "Schedule Link". Seems its correctly taking from Column B

  6. #6
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Have you removed your notes :

    No i have not removed
    Note
    Column C need as active column as its not fixed one. I am requesting dont hardcode as C. It may be selection of range of Cell or Active Column

    Column D also not fixed one it should be next of Active Column: Hypher link should update next column of the above

    Column B Sheet Names are not fixed one will vary based on the sheets available in the workbook - its for reference only.

  7. #7
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    Getting error at this row

    Please Login or Register  to view this content.
    That's why I ask, have you remove your text in column C ?

    EXCEL_2020-05-22_17-40-02.png


    And Column B is for just reference i have added,
    if i remove column B text, not getting Hypher link.
    What column B ?
    I thought at the first time before running the macro there will be nothing in column B,
    as column B row is going to have the sheet name by automation.

    Expecting to get Hypher link based on Column C reference Ex "Sheet1!$G$11"
    In what column did you expect the hyperlink ? column B ? or column D ?

    Based on your workbook, the hyperlink you want to appear is in column D.

    Sorry I don't understand what you mean .

    I thought that your first condition before running the macro, sheet Summary will look like this :
    EXCEL_2020-05-22_16-34-25.png

    So, there will be nothing in column B and D. Nothing at all.
    Not even your notes in columns C of the sample workbook like this :
    Column C I am having Formula Linked numbers within workbook of other Sheets
    Column D required Hypherlink of formula linked in column C (Same Row)
    Similarly on Source Sheet to Summary Sheet required Hypher link on defualt F1



    Note
    Column C need as active column as its not fixed one
    Column D also not fixed one it should be next of Active Column
    Column B Sheet Names are not fixed one will varry based on the sheets available in the workbook
    And Hypher link Text To Display i am requesting Worksheet Name + "Schedule Link".
    Seems its correctly taking from Column B
    Sorry... I don't understand, because in my side after running the macro,
    the text in Cell D5 is "Sheet2 Schedule Link" , cell D6 is "Sheet3 Schedule Link", cell D7 is "Sheet4 Schedule Link".

    There is no plus (+) sign.
    So do you mean that text to display in cell D5 is "Sheet2 + Schedule Link" ?
    cell D6 is "Sheet3 + Schedule Link" ? and so on ?

    Sorry I don't understand what you mean.

    Besides, you first told me like this :
    I am getting Hypherlink in Summary Sheet
    So it worked or not actually ?
    but after completion of Macro i am getting the below Error
    in my side, it didn't throw me an error.
    I suspect that you haven't removed your text in column C13 to C22, as seen in the first image above .

    Column C must only contains your formula, starts from cell C5 to whatever of the last cell...
    so maybe the last cell with formula is C100 or C5000 or C25000 etc, because as you said it is not fixed.
    But in your sample workbook, in column C13 to C22 you put a notes there.
    If you haven't remove that notes, then the macro will throw an error.

    Sorry, if I'm mistaken to get what you mean

  8. #8
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    Your understanding is correct. I removed column D in sample work book to test the macro, First Time i got Hypher link but now i am not getting Hypher link. Hypher link is showing only D5 remaining rows not coming.


    So do you mean that text to display in cell D5 is "Sheet2 + Schedule Link" ?
    cell D6 is "Sheet3 + Schedule Link" ? and so on ?

    Yes you are correct




    I thought that your first condition before running the macro, sheet Summary will look like this :
    Name: EXCEL_2020-05-22_16-34-25.png
    Views: 0
    Size: 8.5 KB

    Yes Exactly Correct

  9. #9
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post

    No i have not removed
    Note
    Column C need as active column as its not fixed one.
    Then that's why it throw you the error.


    I am requesting dont hardcode as C.
    It may be selection of range of Cell or Active Column
    Oh... that's what you mean.
    Sorry, I just get it now . Because I thought the "not fixed" on is the row of the column C, not the column itself.

    Column D also not fixed one it should be next of Active Column: Hypher link should update next column of the above

    Column B Sheet Names are not fixed one will vary based on the sheets available in the workbook - its for reference only.
    Ok... so do you mean something like this ?

    In Summary sheet, there will be a formula, but the location of the formula is not known if the workbook is not open yet,
    because the location could be anywhere in the Summary sheet. Maybe the formula starts from Y100 to Y10000,
    maybe the formula starts from B1 to B325, maybe the formula starts from K50 to K2000, and so on.

    So, before the code do the process, the code need to be told first by the user
    about WHERE is the starting point of the formula cell.

    So, for example - after the user tell the code that the starting cell of the formula is Y100,
    then the code should make X100 (to whatever how many rows the formula in column Y) have the sheet name
    and Z100 (to whatever how many rows the formula in column Y) have the hyperlink.

    Or if the user tell the code that the starting cell of the formula is B10,
    then the code should make A10 (to whatever how many rows the formula in column Y) have the sheet name
    and C10 (to whatever how many rows the formula in column Y) have the hyperlink.

    Something like that what you mean,ayyappan80 ?

  10. #10
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Just created another excel file in the same format and tested only D5 i am getting Hypher link, not in remaining rows. Similarly on worksheets the F1 link also not generating. Sorry may be i was look wrong file and confirmed you in the beginning
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    So, for example - after the user tell the code that the starting cell of the formula is Y100,
    then the code should make X100 (to whatever how many rows the formula in column Y) have the sheet name
    and Z100 (to whatever how many rows the formula in column Y) have the hyperlink.

    1) Yes user can tell the Start line as Y100 (its having formula)

    2) then the code should make X100 (to whatever how many rows the formula in column Y) have the sheet name: Its not required to make the sheet name on column X.

    3) and Z100 (to whatever how many rows the formula in column Y) have the hyperlink. Yes Absolutely

  12. #12
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    ayyapan80,

    Now it need user interaction before running the code, which is :
    before running the code, the user must click/activate the cell where the first formula appear.

    For example, the list of the formula is in cell D50 to cell D1000.
    Then the location of the first formula appear is cell D50
    ---> the user must click this cell D50 before running the code
    in order the code know where to start the process.

    If say the list of the formula is in cell K1234 to K258941
    Then the location of the first formula appear is cell K1234
    ---> the user must click this cell K1234 before running the code
    in order the code know where to start the process.

    etc.

    Please try this one on your second sample workbook, Sample2.xlsx ... so the sheet is in "clean" state.
    Please Login or Register  to view this content.
    i hope that's what you mean.
    Last edited by karmapala; 05-22-2020 at 06:42 AM.

  13. #13
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    The recent code is working fine.

    Attachment 678967

    But its adding name of worksheet to left of the column on which i am running the VBA .

    Can you please exclude that or if its must required for this code can you please put next to hypher link column? because i will have some data in left column.
    Attached Images Attached Images

  14. #14
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    I am sorry i forget to tell you, on formula column i will have some blank row also. So the hypher link should create only on rows which i am having formula.
    Rows as you said i am selecting manually.

  15. #15
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    can you please put next to hypher link column?
    because i will have some data in left column
    You can do it by yourself
    by changing this line :
    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    This will put the sheet name to the right column where the hyperlink is.

  16. #16
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    I am sorry i forget to tell you, on formula column i will have some blank row also. So the hypher link should create only on rows which i am having formula.
    Rows as you said i am selecting manually.
    try this :

    Please Login or Register  to view this content.
    Last edited by karmapala; 05-22-2020 at 07:36 AM.

  17. #17
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    If i have blank rows then i am getting hypher link at first row only.
    snapshot attached.

    before running the code

    Before Running the Code.PNG

    after running the code

    After running the code.PNG
    Last edited by ayyappan80; 05-22-2020 at 07:17 AM.

  18. #18
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    ayyappan80 ,

    Please try the code on my reply #16

  19. #19
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    Thank you so much for your help the code is working perfectly now.

  20. #20
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Glad that i can help, ayyapan.

    Please don't forget that in the column where the formula is must contain only a formula or a blank cell. No other kind value, such as numer, text, etc.

  21. #21
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    On my original file not getting hyperlink in all rows, getting hyperlink at 1st row only.

    and runt time error 9 coming on the below line

    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Just found, if sheet name is lengthy then above error is coming. can you please fix this error

  23. #23
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    If i remove the below line in my orginal file too the code is working without any error. But i am not getting Hypherlink in sheets to summary file.

    Please Login or Register  to view this content.
    Please help to correct the code.

  24. #24
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    Just found, if sheet name is lengthy then above error is coming. can you please fix this error
    To be honest, I haven't found another way on how to solve it.
    I think it's not because the sheet name is lengthy, but the sheet name has a blank space.

    Only this if I may suggest you :
    If your sheet name has a blank space, try to change the space with underscore sign ---> _
    on which I hope it will work.

    Just now I've tried by adding a code like this :
    Please Login or Register  to view this content.
    In my side the code is working although the sheet name has a blank space.
    I'm not sure if it's also working in your side.

    Just try to add the above code below this code :
    Please Login or Register  to view this content.
    Last edited by karmapala; 05-22-2020 at 11:46 AM.

  25. #25
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    I have more than 100 sheets. If any other members support to fix this error it will be more helpful.

  26. #26
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    I have more than 100 sheets. If any other members support to fix this error it will be more helpful.
    Do you mean that you've tried to add the code (I mentioned on my post #24) and still fail ?

  27. #27
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Yes Same Run Time Error9 is coming.
    What you are saying is correct my sheet name i have space, some sheet name like "sheet-4" and sheet_4 like this. Excluding sheet name on hyperlink will solve the problem?

  28. #28
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Only this if I may suggest you :
    If your sheet name has a blank space, try to change the space with underscore sign ---> _
    on which I hope it will work

    @ I added underscore in my all sheets with the below code

    Please Login or Register  to view this content.
    Still i am getting the run time error 9

  29. #29
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi

    I found the issue, in some formula on my original file start with like this, "=0-"

    Please Login or Register  to view this content.


    the code showing error and stooping at that row and showing runtime error9

    Can you please include in your code to consider the above formula type also.


    Please help
    Last edited by ayyappan80; 05-22-2020 at 12:32 PM.

  30. #30
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    I just remove "0-" from the formula, code is working fine without any error. but i need this formula. So kindly include this formula type in code. Sorry to not informing early about this formula type.

  31. #31
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    Hi

    I found the issue, in some formula on my original file start with like this, "=0-"

    Please Login or Register  to view this content.


    the code showing error and stooping at that row and showing runtime error9

    Can you please include in your code to consider the above formula type also.


    Please help
    The problem is, in my side the macro runs without error,
    even after I change a sheet name to 0-Sheet4,
    I also change another sheet name to fdhygfjfjyfhydfgsfdrtdryg (lengthy)
    I also change another sheet name to ewstfdvb srsdfg rg (has space)
    I also change another sheet name to tpdhy_yrdmdf_we (with underscore, it runs ok).

    2020-05-23_01-40-14.gif

    Are you sure you already ADD the code to replace ' sign to nothing ?


    2020-05-23_01-40-54.gif
    2020-05-23_01-41-18.gif

    Please have a look at the xlsm attachment then press the button.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    even after I change a sheet name to 0-Sheet4,
    @i am saying where the formula is linked in Summary sheet, i have added 0- and not in sheet name.
    I have just added in sample workbook also, There also its showing error.

    Issue is not because of sheet name. on summary sheet when i paste link the formula, before formula i have added 0- sheet name is remain same.
    Attachment 679122
    Attached Files Attached Files

  33. #33
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    Hi karmapala
    @i am saying where the formula is linked in Summary sheet,
    i have added 0-
    and not in sheet name.
    I wonder why did you do that ?

    Say... when the first time the workbook is open,
    the formula in cell C5 is =Sheet4!$B$4 as seen in the image below
    EXCEL_2020-05-23_15-05-18.png

    Before running the macro, you change the formula by adding 0-
    so the formula becomes like this =0-Sheet4!$B$4, as seen in the image below.
    EXCEL_2020-05-23_15-08-04.png

    I wonder, why did you do that ?

    Adding 0- to that formula is telling to calculate [zero minus what is in the Sheet4 cell B4]
    The result in C5 then -4664646.

    If you change the formula to =500-Sheet4!$B$4
    then the result in C5 is -4664146

    If you change the formula to =X-Sheet4!$B$4
    then the result in C5 is #NAME?
    Do you know why the result in C5 "#NAME?" if you change the formula from original =Sheet4!$B$4 to =X-Sheet4!$B$4 ?

    I have just added in sample workbook also, There also its showing error.
    There is something wrong in your side.
    Because in my side the macro runs without throwing me an error.

    Issue is not because of sheet name.
    You said in your post #22:
    "Just found, if sheet name is lengthy then above error is coming"

    before formula i have added 0- sheet name is remain same.
    I never thought that you do something first to the formula before running the macro.

    The code to be run is in the assumption that the formula in the Summary sheet will always have the same pattern,
    which only consist the sheet name and the cell location as seen in all of your formula in your first sample workbook
    which I list it on column E for reference, as seen in the image below :
    EXCEL_2020-05-22_16-34-25.png

    Now you add 0- to the formula ---> =0-Sheet4!$B$4
    Then later on maybe you add XXX- to the formula ---> =XXX-Sheet4!$B$4
    or even later on maybe you make a vlookup to the formula ---> =vlookup(Sheet4!$B$4,...,...)
    and so on


  34. #34
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Hi karmapala

    i am not saying your code is not working in sample file. Its working as expected.

    but when i try with my actual workbook i am getting this error. Initially i thought its because of lengthy of worksheet later only i found that i have this "=0-" in my formula. its not in all cells its coming in few row, the reason why its 0- is for presentation of positive and negative on summary sheet. Now i am requesting to consider this type of formula also in your code, if its not possible. Then its fine, i will do some alternate.

    However the code which you provided is 100% working on other rows which is not having 0-

    Thanks for support

  35. #35
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    Quote Originally Posted by ayyappan80 View Post
    the reason why its 0- is for presentation of positive and negative on summary sheet
    If the pattern to have the formula result in negative is always the same, which is "0-",
    then why don't you just do the thing which is the same thing on the code I added,
    after I've found out that if the sheet name has space, the macro throw error ?

    I add the code below
    Please Login or Register  to view this content.
    after you tell me that there is a sheet name with space.
    A sheet name with space is causing Excel put ' sign before and after the sheet name in the formula.
    So in order the code runs, the ' sign need to be replaced to nothing as in the code above.

    Now if you want to put a negative result and if you consistent that you will always do it by add "0-"
    Then you can insert one more code line to replace the "0-" into nothing.
    Please Login or Register  to view this content.
    But if later on you say that you want the result is (for example) from a multiplication,
    so you alter the formula into something like this =0-123*Sheet4!$B$4,
    then the code will AGAIN throw you an error.

  36. #36
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Create HypherLink based on Formula Link of Worksheets in Same Workbook

    you consistent that you will always do it by add "0-"

    Yes always it will be like this only

    Added this line getting 100% result now thanks

    If InStr(ShName, "0-") Then ShName = Replace(ShName, "0-", "")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 10-20-2015, 11:01 AM
  2. Create a new workbook based off of column information across worksheets
    By bbarth in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2015, 03:23 PM
  3. create worksheets and renamed based of list from other workbook into a template
    By floresp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2015, 03:32 PM
  4. [SOLVED] Create new worksheets, name, and link
    By Nitsirk82 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 09:12 AM
  5. How Do I create Buttons To Link Worksheets?
    By modytrane in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2008, 01:46 AM
  6. [SOLVED] how do I link worksheets in the same workbook?
    By vada in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-20-2005, 01:05 PM
  7. [SOLVED] Link multiple worksheets in one workbook to another workbook and .
    By HeatherCarr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1