+ Reply to Thread
Results 1 to 73 of 73

copying hyperlinks.

  1. #1
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    copying hyperlinks.

    made a template that I will use to record daily finances. There are two worksheets that comprise my "MASTER SHEET." Ostensibly, I would copy this template for every working day of the year. Each new day will have new data and a different worksheet name. Everything but the hyperlinks transfers without a problem.

    My MASTER SHEET has hyperlinks that link to specific cells within the MASTER SHEET. When I make a copy of this 'MASTER SHEET' I want the hyperlinks to link to the same specific cells within the new copy of the 'MASTER SHEET' which I then need to rename to match the newly entered financial data.


    PLEASE help me! I have everything, but this puzzle I cannot defeat. Any help would be much appreciated!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    If you create the hyperlinks via the HYPERLINK function you will find your life is a lot simpler...

    For ex.. let's say on MASTER SHEET in A10 you want a HYPERLINK to Z10, so

    A10:=HYPERLINK("#Z10","Hyperlink to Z10")

    If you create a copy of MASTER SHEET you will find the link in A10 links to Z10 on the current sheet at all times.

    Without knowing more it's hard to comment further.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Do not post the same question in multiple forums
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    i need to make a hyperlinks that will link to specific cells on another worksheet within the same workbook. I cannot figure out how to do this using the function route.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Quote Originally Posted by vasmax32 View Post
    i need to make a hyperlinks that will link to specific cells on another worksheet within the same workbook. I cannot figure out how to do this using the function route.
    I can only presume this is an additional question else you are contradicting your original post.

    If the sheet names are constant

    =HYPERLINK("#'SheetName'!CellReference,"TexttoDisplay")

    alter the values above accordingly... when the sheet on which the above resides is copied the link will remain constant and still link to the sheet & cell as referenced on MASTER SHEET.

  6. #6
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    what if the sheet names are not constant?

    my original template was based on a particular date (11th of january). My plan is to make two worksheets for each working day of the year. My work sheets are 11(a) & 11(b). My goal is to be able to copy these to 12(a) & 12(b), 13(a)....etc. I have successfully made links using the ctrl + k hyperlink shortcut, but i cannot figure out why it isn't working with what you provided. I will attach the file. It is in russian, but the principle is the same. There are two passwords. 1st: 1234 2nd: 4321

    Let me know if you can get it to work, please.
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    As I said you will need to use the HYPERLINK function in the appropriate cells to create your links if you wish for the cell references to "adapt" as you copy the sheets

    It's not clear to me on your file where the links are that you wish to adjust but if you wish to link 12(b) to 12(a) and then when you create 2 new (a) & (b) sheets by copying 12(a) & 12(b), subsequently renaming to 13(b) and 13(a) and that in turn the links on 13(b) should adapt from being pointed to 12(a) to 13(a) you must use the current sheet name to assist you...

    To help do this you could for ex. create a Named Range:

    Name: _sheetname
    RefersTo: =TRIM(MID(CELL("filename"),FIND("]",CELL("Filename"))+1,255))

    You can use this dynamic name to help you create Hyperlinks... to illustrate how it works go to a blank cell and enter:

    =_sheetname

    So... let's say you wanted to create a Hyperlink to (b) sheet from (a) sheet to cell A10:

    =HYPERLINK("#'"&SUBSTITUTE(_sheetname,"(a)","(b)")&"'!A10")

    the above will always link to the (b) sheet of the same (a) sheet

    so it follows the opposite would hold true for linking from sheet (b) to sheet (a):

    =HYPERLINK("#'"&SUBSTITUTE(_sheetname,"(b)","(a)")&"'!A10")

    the above entered on (b) sheet will link to A10 on respective (a) sheet.

    The key thing here is that other than (a) & (b) the sheet names are identical.

    I hope that helps.
    Last edited by DonkeyOte; 01-13-2009 at 09:46 AM.

  8. #8
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Ok, so I am a completely and hopelessly lost newb! I have tried creating a name range and following your instructions, but it always says it cannot open the specified file.

    The way I did the links was so that each day the "(a)" page showed all the outcomes. If there was ever a question then you could click on any given total and it would take you to the more detailed "(b)" page.

    Right now, if I copy and paste the original two worksheets (11a & 11b) and then just edit the name to the next working day (12a & 12b) when I click on a link on any subsequent day it will always go back to the original 11a or 11b.

    Would it be too much to ask just to do one link for me so that I could see how it works in my case? I will then hopefully understand how to formulate it so that I can move on.

    This problem has already cost me two days of lost time; I really cannot believe how complicated this turned out to be for me.

    Again I attached the file. Passwords are 1234 & 4321. I am sorry that I'm so dimwitted.
    Attached Files Attached Files

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    For now let's ignore the notion of using a named range / constant re: sheet name... attached is an example of how you can use HYPERLINK function to create HYPERLINKS and in doing so create links that can adapt *relatively* easily (pardon the pun) ... if you copy 12(a) & 12(b) you should find the links on the A sheet have "adapted" to point at the new (b) sheet.

    In your case what you're doing is not straightforward for Excel to understand... essentially you're trying to create a relationship between two sheets and you want the relationship to hold and yet adapt simultaneously as you copy those 2 sheets over and over ... this isn't a trivial action for XL to cope with.... that said the HYPERLINK function can be fiddly to begin with but once you get the hang of it you'll find you can do quite clever things with it.

    Anyway - there are ways to make the formulae more elegant but I hope it points you in the right direction if nothing else.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Thank you!!!

    Donkey,

    You, my friend, are a great hero to me now! Thank you so much! I have finally been able to continue building the structure without being perplexed by these damned links. Thank you for being so patient with me, and even going as far as giving me a working sample!! A thousand times thank you Farewell for now.

    From Russia with gratitude,

    Vasili

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    никакие проблемы

    (ok... I googled it... edit: I hope it means what I think it means...)

  12. #12
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    a snag!!

    first off, well said and thank you again.

    so i did everything i wanted to and then i copied and pasted, but i found a snag.

    all the links from the "a" to "b" pages work, but on the copied pages the links from "b" to "a" go back to the original worksheet.

    check out this new file. the problem is with the "b" worksheet (ie 13 b)

    password is still 1234

    is there anyway to fix it?

    Again, i appreciate your help enormously!

    -Vasili
    Attached Files Attached Files

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    You have not inserted the required formula in K3 such that it reflects the correct sheet name:

    K3: =TRIM(MID(CELL("filename"),FIND("]",CELL("Filename"))+1,255))

    Note also that SUBSTITUTE is case sensitive, so:

    =SUBSTITUTE($K$3,"(B)","(A")) --> where K3 = 12b result = 12b

    =SUBSTITUTE($K$3","(b)","(a)") --> where K3 = 12b result = 12a

    However given you can't have a sheet 12B and sheet 12b you could just as easily coerce the string into Upper Case if you prefer:

    =SUBSTITUTE(UPPER($K$3),"(B)","(A)")
    Last edited by DonkeyOte; 01-14-2009 at 12:11 PM.

  14. #14
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    donkey,

    As before you have saved me! I cannot believe that the error was coming from me using uppercase instead of lowercase!

    I am now copying each day for the rest of the year. It is tedious and very slow work. For each copy I have to edit four things: 1 & 2: Change the names of the worksheet to correspond with the dates. 3 & 4: On the "a" sheets I change the dates on top of the two boxes. The top box is incomes so the date corresponds to the beginning of said day. The bottom box is expenditures, so we decided to list all the expenditures at the end of the previous business day.

    Anyways, is there some sort of way to expedite this? I don't mind doing it all manually, but I will have to repeat the damned process every year.

  15. #15
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    here is the file as it looks right now.

    eventually, i want to make an overview file of the totals.

    the scale will go like this:

    daily transactions --> weekly --> monthly --> quarterly --> annually

    I bet I will have a few more link questions in the near future as i progress.
    Attached Files Attached Files

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Again my lack of proficiency in Russian makes certain things hard to evaluate but if we assume that you create a file and you create 2 sheets manually (namely 1(a) and 1(b)) and you want to then use those 2 sheets to create sheets for up to 31 days...(I'm assuming you have 1 file per month given this file is called January) ... well then you could use something along the lines of:

    Please Login or Register  to view this content.
    Note a few things:

    1 - I made the code Private such that it's not available to be run from native XL (you would need to go into the VBEditor to run it)

    2 - I don't know how you've formatted the Date headers as they're not Date Values (ie ISTEXT(A2) = TRUE) so I can't comment.. but you can use your incremented variable to create a Date Serial and format -- in the above example A2 on say 23(a) sheet would read in English: Friday, 23 January 2009 and A16 would read Thursday, 22 January 2009

    I hope this points you in the right direction.

  17. #17
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    thanks a lot for the link. i will take a look at it a little later.

    I started to input some data to test out what i have currently, and i found something that i missed completely

    on the "b" page all the numbered links are meant to have two functions : 1. they need to show the sum of their respective columns. 2. they act as a hyperlink to their appropriate destination.

    is there a way to combine these two?

  18. #18
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Not entirely sure I follow but if for ex. you mean:

    16(b): B16 link should display the SUM of B4:B15 as well as linking back to the summary value on 16(a) then change the "text to display" element of the Hyperlink to be a SUM function, eg:

    =HYPERLINK("#'"&SUBSTITUTE($L$1,"(b)","(a)")&"'!"&$L3,$B16)

    becomes

    =HYPERLINK("#'"&SUBSTITUTE(UPPER($L$1),"(B)","(A)")&"'!"&$L3,SUM(B4:B15))

    is that what you meant ?



    Now I look at your links I think you have this issue all over the place... eg

    16(a) B4 should really be using the contents of the above cell (ie 16(b)!B16) as it's "Text to Display", eg this:

    B4: =HYPERLINK("#'"&SUBSTITUTE($L$1,"(a)","(b)")&"'!"&$M4,$B4)

    will need to become this:

    B4: =HYPERLINK("#'"&SUBSTITUTE(UPPER($L$1),"(A)","(B)")&"'!"&$M4,INDIRECT("'"&SUBSTITUTE(UPPER($L$1),"(A)","(B)")&"'!$B$16"))

    so as to display the total from the (b) sheet in the (a) sheet summary.

    Note in all of my examples I have used UPPER on the Substitute -- this will cater for any mixing of case on sheet names -- ie should 16(a) be renamed to 16(A) the old substitute would not work... you can't have a sheet 16(a) and 16(A) as XL doesn't differentiate sheet names by case... given that fact and given that Substitute is case sensitive you can force the sheet name into UPPER case before doing a substitution... this way 16(a) is handled as 16(A) (as is 16(A) obviously)

  19. #19
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    almost there

    donkey,

    You were right on all the assumptions you made. I haven't been using "UPPER" because i went back and manually edited all the uppercase letters back to lowercase.

    I have finished updating the formulas on the "b" page, but i tried to use the formula for the "a" page, but it doesn't jibe.

    I have made a sample page which is in english. Password is still the same.

    I can't repeat it enough: thank you so much for all the help!

    -Vasili
    Attached Files Attached Files

  20. #20
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    A few typos I think...

    Using English delimiter ie comma as opposed to semi-colon it would be:

    =HYPERLINK("#'"&SUBSTITUTE($G$1,"(a)","(b)")&"'!"&$H4,INDIRECT("'"&SUBSTITUTE($G$1,"(a)","(b)")&"'!$B$16"))

    pay particular attention to " ' " at the commencement of the INDIRECT (without spaces obviously) -- you had " " ' in your file

  21. #21
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    I tried your formula again (copy and paste) and still didn't work. could you try to make one cell work in my file, please? I will look at it closer then.
    Attached Files Attached Files

  22. #22
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    on the file you sent earlier you had this in 11(a) B4

    =HYPERLINK("#'"&SUBSTITUTE($G$1;"(a)";"(b)")&"'!"&$H4;INDIRECT(""'&SUBSTITUTE($G$1),"(a)","(b)")&"'!$B$16"))

    change to (or copy & paste)

    =HYPERLINK("#'"&SUBSTITUTE($G$1;"(a)";"(b)")&"'!"&$H4;INDIRECT("'"&SUBSTITUTE($G$1,"(a)","(b)")&"'!$B$16"))

    I have highlighted the errors in the initial entry by colouring red & bold.

    I have not tested the above given I am running in UK with , as delimiter as opposed to ; but it should work.

  23. #23
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    hmmm,

    i tried to copy and paste and it still didn't work. i tried changing the ";" to all "," and vice versa. the best i could get was a reference error. if you do it in the spreadsheet itself the differences between our versions would be adjusted automatically, no?

    i'm sorry i keep imposing on you, but i am lost at sea, haha!

  24. #24
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    There shouldn't be a space between the $ and B$16 in the second part (the INDIRECT)... it's just how this forum displays after a certain length... remove it and see if it works.

  25. #25
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    That was it! Thank you, I can go home finally! I will finish this up tomorrow. This is not easy stuff; how do you know all this?

    Also, is it okay to keep asking for help? I really really appreciate how much you've done already, so I do not want to abuse that. I think at some point I will have to send you a bottle of vodka to thank you!

    -Vasili

  26. #26
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Quote Originally Posted by vasmax32
    This is not easy stuff; how do you know all this?
    I don't get out much.

    Quote Originally Posted by vasmax32
    Also, is it okay to keep asking for help? I really really appreciate how much you've done already, so I do not want to abuse that. I think at some point I will have to send you a bottle of vodka to thank you!
    -Vasili
    That's why we loiter in these virtual halls... though taking 24 posts to resolve your question doesn't make me look very efficient ... ;-)

    as for the Vodka - no you're ok it sounds like you need it more than I do...

  27. #27
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    I appreciate your willingness to put up with a noobie like myself.

    I don't know how, but I've managed to find yet another snag once I started copying the worksheets. The links worked perfectly before I started to copy, but with multiple worksheets sometimes the links go to the wrong worksheet.

    For instance, if I click on 12(a) then on 12(b) it works, but if I click on either 11(a) or 11(b) directly afterwards then the links take me to back to 12 instead of staying on 11.

    Did I trip up on my link formulas somewhere? I'm reviewing right now as well...

  28. #28
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    I would need to see the file before I could comment... they key will be to ensure that you are storing the formula to determine the sheet name on each sheet...

  29. #29
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Oops, I knew I forgot something

    All the headers and names are back in russian, I'm afraid. So, if that's an issue the fin test files I put up earlier have english translation.
    Attached Files Attached Files

  30. #30
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    hmm... my bad really.. the CELL("filename") approach is flawed... it seems if you calculate the sheet name on one sheet it applies the result of that calculation to all other cells in the workbook using the same formula... eg you calculate on 12(b) --> every other sheet in the workbook will have sheet name of 12(b) in H1/L1 cell whereas I had assumed the file name approach would return the sheet name relative to each formula location... not so... so as I see it you have a couple of options:

    1) Physically enter the sheet names into H1/L1 (a)/(b) respectively...
    you could do this as part of the sub routine I provided earlier if you were automating this process of sheet creation...

    2) Use a UDF approach, eg (in Module):

    Please Login or Register  to view this content.
    Then in H1/L1 etc

    =SheetName(H1)

    That "should" work I think.

    Also on a final point -- to avoid issues of circular references I would advise you store the "Text to Display" on the (a) sheets in separate cells such that the HYPERLINK function does not have to refer to itself... eg copy A4:A10 & A18:A24 and paste values say to Col F (hidden) -- then change the links such that for text to display value (last parameter) they look to F4:F10 & F18:F24 for values rather than referring to themselves, eg:

    11(a)!A4: =HYPERLINK("#'"&SUBSTITUTE($H$1,"(a)","(b)")&"'!"&$H4,$F4)

    and so on and so forth.... you can then dispose with need to enable iteration.

    Hope that helps.

  31. #31
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    As has just been pointed out to me -- I'm being a bit of a newbie myself...

    If you reference a cell on the active sheet in CELL("filename",cell ref) you will avoid the error I was talking about (thus no need for UDF etc...)

    So go back to the old method and insert the following in H1/L1 cells:

    =TRIM(MID(CELL("filename",$A$1),FIND("]",CELL("Filename",$A$1))+1,255))

    Apologies...

  32. #32
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    sorry i didn't see your last message. Ok, i tried to do what you said, but i get an error saying that i have too few arguments

    hmm, i am trying to fix this, but i don't see the error quite yet...
    Last edited by vasmax32; 01-16-2009 at 10:15 AM.

  33. #33
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    I thought I had posted on friday before I left that everything was okay. I guess it didn't update the thread for some reason. Anyway, I had another small question about deleting macros.

    I would like to delete a macro, however, the delete button is inactive on the macro dialogue box. Do you know how to activate this button?

  34. #34
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    actually, nevermind. I went through visual basic editor and found how to delete.

  35. #35
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    OK ... so where are you on the project now ? Have you resolved all of your link issues etc ?

    Once you're happy that you have both (a) & (b) sheet setup correctly you could think about creating your monthly files (if you are indeed doing monthly files)... the code I posted way back when was setup such that you would have a file with 1(a) & 1(b) setup -- then run the macro having specified the month number and the vba would then generate the remaining days sheets for you (ie 2(a) right through to 31(b) for Jan).

  36. #36
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Donkey,

    First off, thank you so much for helping me finally get my daily templates all worked out. For me, I now have a great foundation for daily financial entries.

    On to the next step... Your questions are exactly where mine are as well now. I mentioned before that I wanted to have more or less the same format for a weekly overview, monthly, quarterly, and yearly.

    Ideally, everything would be linked together so that if there arises a question about any number in the system, it can be quickly found by a series of carefully structured links. All the way from the yearly overview to an individual daily entry if need be.

    Check out the new file I've made for the last month of last year. In the beginning of the worksheets I have a prototype of what I would like for the weekly template to be like.
    This would be replicated four times a month/a workbook (obviously). If you look at the "b" sheet you will notice that there is a date column. I would like to link the date with the particular day the data was taken. I made this link by using the hyperlink wizard, not through the formulaic way.

    Is there a way to make a macro to do this stuff? I didn't stop to think about the sheer amount of manual editing this would take. Yet, because each week is a little bit different I don't know how to make a macro (in addition to having very little experience with macros other than recording keystrokes )

    What do you think?

  37. #37
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    You forgot to upload the file I think ?

  38. #38
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    I'm somewhat of a retard... :)

    Yes, I did forget to post it. Here it is attached.
    Attached Files Attached Files

  39. #39
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Any chance for x-referencing purposes that you could post up an equivalent in English ? That way I can get a better feel for what I'm looking at.

  40. #40
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Here's the english version.

    I only fully translated the weekly tabs, and the first day tabs, the other four days I left in Russian, but the format is the same.

    As it turns, out I saw an error I had previously overlooked.

    If you look at the "b" pages you will notice that the dates for each company are linked back to the 1(a). This is wrong. I wanted them to be linked to the (a) page, not specifically the 1(a) page so that they would show their respective date. Was that clear, or am I just confusing?
    Attached Files Attached Files

  41. #41
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    So if I've understood correctly... are you looking for something like:

    Please Login or Register  to view this content.
    The above copied across range C4:E10 should give you your first company... for your 2nd company change the formula in the top left most cell in the 2nd table to use K$4 rather than K$3 (then copy across 2nd table)
    You could make most of these formulae a little more elegant and transferable in terms of the ease with which you can copy & paste one formula over the entire range of hyperlinks but perhaps we should leave that for another day ... ;-)
    Last edited by DonkeyOte; 01-20-2009 at 09:56 AM.

  42. #42
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Donkey,

    You never cease to amaze me. You are a formula wizard! That is quite a formula you have made; thank you!!

    There is a minor qualm that I have found with respect to some linked cells. I can't seem to figure it out. If you look at the "b" pages (daily entries). For example, the A2:F2 merged cell ideally should copy the date from the A2:D2 merged cell on the corresponding "a" page so that the current date is displayed. I cannot figure out how to unlink the cells from only the 1(a) date.

    I have attached the semi-updated (I am currently finishing it) file that you saw yesterday.

    Thanks.
    Attached Files Attached Files
    Last edited by vasmax32; 01-21-2009 at 07:09 AM.

  43. #43
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    If I've understood I think you're saying that each (b) sheet should have the following formula in A2:

    Please Login or Register  to view this content.
    Note that although the cell is "merged" across x cells the value within that merged area resides only in the left most cell of the merged range - hence the above references only A2 as that is where the value is.

  44. #44
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    On to the next level in the pyramid

    Alright, I am on to the next step: the monthly overview. I tried applying your weekly formulas, but I couldn't figure it out. What do you think? Again, thank you so much for the help!
    Attached Files Attached Files

  45. #45
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Again if I've assumed correctly you need simply remove the DAY( ) from around the references to $Bx... ie change:

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    From what I can tell the Month(a) sheet seems ok, no ?

  46. #46
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    so far, all your assumptions have been exactly in-line with my intentions. I will take a look at these and see how they work; thanks.

  47. #47
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Donkey,

    That was exactly what I needed; thank you. I had a misc question about the actual clicking of the links: is there any way to highlight the cell on the other side of the link? What I mean is that sometimes when I click on the link it takes me to the corresponding page, but it can be somewhat misleading because the cell I am connecting to is not displayed in the center of the screen, but to the extreme upper part of the screen. This can be confusing because you have to be consciously aware of which company you are trying to look at. Is there a way to remedy this?

  48. #48
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Unfortunately from what I can tell the HYPERLINK function does not invoke the SheetFollowHyperlink event which you could have applied at Workbook level... so given that fact I can't think of anything immediate... someone might suggest a SelectionChange event to keep the activecell formatted a certain colour but that's incredibly risky and will slow your file given it fires to all intents and purposes all the time...

  49. #49
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Donkey,

    I had a realization that my system did not have a very important factor. Although, the day-to-day activity will be accurately accounted for, you cannot yet look at this system and quickly understand how much money is currently in each bank account of each company.

    I created a formula to record this, but again, my little experience with making links requires me to ask for your help. I am assuming that in order to replicate this page for every day in the file I will need another variation of the link formulas that you have shown me.

    The two new pages I made to serve as a template are named "1" and "2" respectively.

    I included some footnotes that are visible on those pages.

    -Vasili
    Attached Files Attached Files

  50. #50
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Vasili...I will review tomorrow... there's no white flag icon ;-0

  51. #51
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Donkey,

    Haha, my friend. I am sorry that I am taking such a toll on you. I definitely know that all my progress is owed exclusively to you. Don't abandon me quite yet!

    -Vasili
    Last edited by vasmax32; 01-23-2009 at 09:33 AM.

  52. #52
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Per my note at Mr Excel please ensure if you x-post elsewhere you provide links accordingly...

    http://www.mrexcel.com/forum/showthread.php?p=1814391

    As you can see ... a number of us frequent multiple forums so they will invariably get spotted.

    Thanks

  53. #53
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    Here's the other link for the other website, but the best representation of the overall file is done here with you.

    http://forums.techguy.org/business-a...s-linking.html


    Also, I had a misc question: is there a way to make the autofill typying function work not only in a particular worksheet, but in an entire workbook?
    Last edited by vasmax32; 01-23-2009 at 09:42 AM.

  54. #54
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Hmmm... FYI most of us don't take kindly to x-posts as we run the risk that we're wasting our time posting a solution here if someone else has already resolved somewhere else and given we do this for free in our spare time this kind of practice will inevitably alienate people.... as I say an FYI.

    From looking at your sample file... it seems to me that you have an error in your formula in 2!B19 -- should I think be referring to B166 not B150... assuming that to be the case then I would advise the following on Sheet "2":

    Please Login or Register  to view this content.
    Then list your linked cells as before in the adjacent cells, eg G17:I23... so

    Please Login or Register  to view this content.
    Once the above has been setup you can apply the following formula on Sheet "2":

    Please Login or Register  to view this content.
    You've not provided any details for column C so I will leave that for you to resolve.

    For Day 1 you will of course get REF# errors using the above however you infer on your pre-existing Sheet "1" that the above range (B17:B23) on Day 1 sheet will contain hard coded values anyway so you will simply override the formulae with your own values.

  55. #55
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    Hey thanks for the formulas! I have been implementing them, and actually they have helped me catch a couple of errors we had made entering in data.

    I did quickly come to a problem, though. What happens to the sheet on every monday?

    EDIT: I figured out the error, at least: I get reference errors because the days skip ahead because of the weekends. The formula refers it to the previous numerical date, but because we don't work all-week long, the gap of the weekend causes the ref! error.

    You can see an example on the "8" tab in the included file. What do you think?

    EDIT: Ok I can't upload the file for some reason... I will upload as soon as it wil let me
    Last edited by vasmax32; 01-26-2009 at 09:44 AM.

  56. #56
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    Apologies for delay - I did not notice you'd added the attachment until just now.

    To be honest this is where you will need to think *creatively*

    IMO a decent approach which would cater for all months, public holidays etc etc would be to create an index sheet - let's call it Sheet_Index
    (you can hide it if you wish)

    Please Login or Register  to view this content.
    What the above will do is generate a listing in Col B of all the Day sheets that exist in each & any file you create.

    Now we can go back and revise the daily sheets further so that we list the last valid prior sheet....

    So let's start with sheet 8 given this was where you were getting the dreaded REF error...

    Please Login or Register  to view this content.
    H1 should return the last day for which a sheet exists prior to current sheet... ie in this case 5

    Now we have that value we must update the other formulae such they utilise the value in H1, so:

    Please Login or Register  to view this content.
    By grouping your daily sheets (excluding day 1) you can implement these changes simultaneously as opposed to updating each sheet on an individual basis.

    I hope that resolves your issue.

  57. #57
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Thumbs up Re: copying hyperlinks.

    Donkey,

    Thank you for that. I had actually found the very low-tier, manual way of editing the formula I had, but luckily before I started changing everything manually I noticed that you had sent a message!

    I will adapt these immediately; thank you!

    I have a small misc question that I was wondering about. Now that the system is functional on the day-to-day basis I have been actively entering data.

    Is there a way to set the auto-fill typing function work not only in a particular worksheet, but in an entire workbook?

    The idea here is that we have the same companies and same reasons from day to day, yet the text auto-fill only works on a worksheet basis, not workbook. I've tried looking online for an answer, but could not. Does this option exist?

    Always grateful,
    -Vasili

  58. #58
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    Not AFAIK.

    If the text values in each cell are pre-determined why not use Data Validation Lists on the relevant cells ?
    See: http://www.contextures.com/xlDataVal08.html for an overview on the subject.

  59. #59
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    Thanks, I will take a look at that and study it so as to understand it and then implement.

    I just finished installing the last of the daily sheets, but I encountered a caveat: on the 1st of every month following December (the first month of our new system) I would like the daily sheet to refer to the last day of the previous month. Is that possible to do in a way that would work automatically?

  60. #60
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    INDIRECT in it's standard form can only target open sources... so if you wanted to dynamically link to the prior month file (variable name) you would need to ensure the prior month file was open at all times (else use morefunc.xll's INDIRECT.EXT function)

    So IMO ... I would say using INDIRECT approach: it's probably not worth the aggravation...

    You *could* in theory create a generic "End of Month" sheet in each monthly file which to all intents and purposes would replicate any other "Day" sheet. To populate the new sheet you would use formulae in a similar vein to the approach we just used to handle the "prior" Day sheet ... ie the "End of Month" sheet would look to the Sheet Index sheet to determine the last valid "Day" sheet in that file... and reference accordingly to populate itself.

    Given you then have a generic fixed reference point (End of Month sheet) for the last day of activity in each monthly file you could in turn manually create links in the current month file to the prior month file and then each month as you create a new file simply:

    Please Login or Register  to view this content.
    That said the above is all dependent upon whether there are "other" links aside from the above which should not be altered...

    (The reason for End of Month sheet is obviously so that the links can remained fixed and only the file to which the references point need be altered... else you woudl face the problem that in some files the last data may be on sheet 27 and in another on day 31 etc...)

    It's up to you... if you feel it's worthwhile then by all means pursue...
    Last edited by DonkeyOte; 01-27-2009 at 06:39 AM.

  61. #61
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    I am trying to make the end of month sheet, but i don't understand the part about how it links to other workbooks. I am including an attachment of what i've done. I will keep looking at what you advised, but so far I'm not getting it.

    -Vasili

  62. #62
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    Vasili, I think from here on you will need to assume the bulk of the work here but in short the End of Month should be based off one of your Day sheets (again, presuming if I've interpreted correctly):

    Please Login or Register  to view this content.
    So to reiterate the End of Month sheet should effectively just replicate any other Day sheet only the values will be based upon the last Daily sheet you have in your file... ie if the last Day was 27 the values in End of Month = values on Day 27 sheet... were the Day 31 the values in End of Month = values on Day 31 sheet.

    So... let's assume this file was for your first month (Dec).

    If you created next months File (Jan) you can manually link your Day 1 table cells to the Dec file "End of Month" cells.. given you know these values = last values in the month for Dec.

    When it comes to creating the 3rd monthly file (Feb) you can open the file and Edit the Links such that you change the "source" from Dec file to the Jan file... the Day 1 values will then reflect the End of Month sheet values from the Jan.xls file rather than the Dec file.

    As I say -- this is all getting a little messy and I myself am not a fan of external links (ever) so if it were me I would be looking at using VBA to pull the values I needed (though I would probably still use the End of Month approach for ease) ... but if you opt to do that (VBA) please create a new thread.

  63. #63
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    Ok, I got it, thanks so much! The files I have currently are linked. While I understand the pitfalls of having external links, I hope that this is simple enough that it won't turn out to be a problem.

    P.S. Did you mean you can't help me further with my questions, or did I misread your message?

  64. #64
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    Quote Originally Posted by vasmax32 View Post
    P.S. Did you mean you can't help me further with my questions, or did I misread your message?
    Not as such... just that I think we've done a fair amount of work on this now and you will need to be comfortable with how it works -- so if you need to do any other adjustments I'd suggest you take a fairly lengthy bash at them yourself first. As is I think you run the risk of ending up with a model that if it were to break you would not know how to resolve... and when it comes to models dealing with cash / revenues / costs no-one should ever be in that position ;-) ... the functions used are not complex per se but the manner in which they are used and interlink within your file is not trivial so there won't be vast numbers out there who "get it" so it's imperative you do ;-)

    As for the links, play it by ear, but performance of your model overall is not going to be great given the huge volume of Volatile functions within (Indirect is Volatile) ... I certainly would not recommend you try and create a Full Year model

  65. #65
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    Fair enough, my friend. I have really appreciated your help on all of this enormously. You make me have faith in the human condition because I am certain I am not the only one you assist with mundane excel problems.

    I do have a concern, though. You said that the model might perform poorly. That is somewhat disconcerting considering the fact that these two months were going to be a template for the rest of the year.

    The way I thought to build this system was akin to a pyramid. If you can imagine it:

    Yearly Overview
    QuarterQuarterQuarterQuarter
    MonthMonthMonthMonthMonthMonthMonthMonth
    WeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeek
    DayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDayDay


    The idea was to build a system that would be interconnected at every level. So that if there ever was a question then it could be answered through a series of clicks. What would you recommend for a venture of this sort?

  66. #66
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    As I said, I think if keep the files to 1 month only things will not be too bad... any more than that and I think performance will begin to deteriorate... I was running your files on a 4GB dual thread machine and the delay in performance was noticeable to me ... and at this point in time that model had only 6 days worth of sheets present (out of say a possible 20-25 days worth).

    The lag in performance is (put simply) the downside of using Volatile functions, in your case INDIRECT (& lots of them) ... if you want dynamic links it's the most obvious approach. The alternative is obviously to setup one monthly file (days 1 to 31 etc) where all the links are setup manually to the appropriate sheets etceteras. If your layout is never likely to change that's not a bad approach, time consuming to setup for sure but nothing more than that. The downside of a manual approach would only appear should you decide at a later date to alter the layout... the INDIRECT approach with HYPERLINK function allows you to adapt all of your links simultaneously whereas were the links created manually updating them is obviously going to take a little longer and will no doubt be a little more tiresome on your part.

    It comes down to preference... as with everything in Excel there are countless ways to "skin a cat" ... which method you pick should be based upon that which you feel most comfortable with given it is you who must maintain it... VBA can often alleviate performance related issues however it is obvious that a VBA approach requires a certain level of expertise on the part of the admin... I'm not casting dispersions here on your own abilities, merely commenting based on what you have said about your own skills, which I believe amount to the fact that you would consider yourself to be an intermediate user... on that basis I would say that IMO VBA is perhaps not worth pursuing at this point in time. If over time performance becomes a real hindrance then yes you should begin to look into other approaches and some sort of VBA approach is most likely to be the best available option.

    Hopefully that didn't sound condescending as was not my intention.

  67. #67
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47
    A couple of misc questions:

    since each file is password protected when I linked the first day of a month to the last day of the previous month, excel requires me to enter in the password for each file that has provided information.

    The acct. system starts in December of 2008. In order to open the Jan. 2009 file a password is entered twice. In order to open the Feb 2009 file a password is entered three times. Am I to understand that by the time we get to the end of the year, a password will have to be entered 13 times?

    If so, is there any way other than to take off the passwords in preventing such a problem?

  68. #68
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    I will post my last misc. question on a new thread. Are you around these days?

    http://www.excelforum.com/excel-prog...ml#post2041555
    Last edited by vasmax32; 02-12-2009 at 05:00 AM.

  69. #69
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    quarterly overview

    Donkey,

    I am now working on the quarterly overview, but I am running into some issues. I am unsure on how to link individual cells when operating between different workbooks.

    I know how to make a link to a particular file, but not to the corresponding cell of number. I will include what I've done so far. There is a disconnect between individual numbers.
    Attached Files Attached Files

  70. #70
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    Vasili, not entirely sure I'm following but if you want a cell to return a value from another file you will need to link to it - ie open the other workbook, go back to the cell you want to insert the value into, type = then navigate to the other file's cell as appropriate and click it... if you then close the linked fill the link in the quarterly file cell will update to show the full path to the linked file. You can't use INDIRECT to a closed workbook.

  71. #71
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Donkey,

    I mis-wrote what I was trying to say. I know how to link the cells values, but is it possible to HYPERlink the individual cells between workbooks?

  72. #72
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: copying hyperlinks.

    AFAIK I don't think you can -- you can only link to the file.

    You could in theory put in a double click event on the cell range such that when double clicked the file (if not open) is launched and the appropriate cell is selected... non trivial I'd say given this thread ;-) ... ie could get quite messy to code.

  73. #73
    Registered User
    Join Date
    01-12-2009
    Location
    moscow, russia
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: copying hyperlinks.

    Ok, I guess I can live with only being able to link to the individual file. My next concern is something that I tried to have answered in a new thread, but didn't get what I was looking for. http://www.excelforum.com/excel-prog...her-files.html

    Basically, I need a way to figure out how to only put in the password once. For the monthly files I am prompted twice to type in the password, but for the quarterly files it is four times, and for the yearly one it will be 13 times. This happens as soon as the file is opened so there's no time to run individual macros. Is there a solution to this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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