Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Worksheets within the same workbook (https://www.askmehelpdesk.com/showthread.php?t=30793)

  • Aug 3, 2006, 11:40 AM
    Wonder84lalala
    Worksheets within the same workbook
    Hi,

    I have many worksheets within the same file, what I really want is to have either a (link / go to option) from sheet1 to other sheets without using hyperlink because I want to send to others.

    Example: if I click on Apple from Sheet 1, I will go to Sheet2

    Sheet1 Sheet2 Sheet3 Sheet4
    Apple Apple Banana Orange
    Banana
    Orange


    Thanks for your help,
    Tuong
  • Aug 4, 2006, 10:19 PM
    worthbeads
    I don't really think there is such a function, but I could be wrong
    :confused:
  • Aug 5, 2006, 04:00 AM
    ScottGem
    Quote:

    Originally Posted by Wonder84lalala
    hi,

    I have many worksheets within the same file, what I really want is to have either a (link / go to option) from sheet1 to other sheets without using hyperlink because i want to send to others.

    Example: if I click on Apple from Sheet 1, I will go to Sheet2

    Sheet1 Sheet2 Sheet3 Sheet4
    Apple Apple Banana Orange
    Banana
    Orange


    thanks for your help,
    tuong

    If I follow what you want, you want to use Sheet1 as a sort of table of contents where you can select an item and go to a sheet with more info. If that's the case, then why not name each sheet and the user can just click on the sheet tab?

    And what does sending the sheet to others have to do wioth not using hyperlinks. If you are linking to areas within the same workbook that shouldn't matter.

    You can, using VBA, set up a button that would find and got to another cell that has the same contents as the currently highlighted cell.
  • Aug 7, 2006, 06:53 AM
    Wonder84lalala
    Thanks for your reply,

    If that's the case, then why not name each sheet and the user can just click on the sheet tab? [I have many worksheets]

    And what does sending the sheet to others have to do wioth not using hyperlinks. If you are linking to areas within the same workbook that shouldn't matter. C38 =HYPERLINK('2006-06-30'!N20) [Cannot open the specified file]

    You can, using VBA, set up a button that would find and got to another cell that has the same contents as the currently highlighted cell. [Can you show me the VBA codes?]
  • Aug 7, 2006, 07:54 AM
    colbtech
    Post a sample of the spreadsheet, you will have to zip it. If it is too big, use save as and delete a load of sheets (leave enough for us to work with)
  • Aug 7, 2006, 08:03 AM
    ScottGem
    I'm not that good on Excel VBA, but the code would look something like this:

    Application.Goto Reference:=Worksheets("ActiveCell.Value").Range("A 1"), _
    Scroll:=True

    That will go to the first cell of the sheer named in the currently selected cell.
  • Aug 8, 2006, 01:38 AM
    colbtech
    1 Attachment(s)
    Is this what you require? Seems to me that it is just as easy to click on the tabs (unless you have lots & lots & lots... & lots of tabs)
  • Aug 8, 2006, 02:35 AM
    cheechthecheechy
    I'm not sure, but this *might* work.

    U know how u can convert spreadsheets to web pages?

    Well u can convert them to webpages and like add links to differnet pages, but that would mean u can't edit them, or can you...

    *twillight zone music*

    Hehehe
  • Aug 8, 2006, 05:37 AM
    ScottGem
    I agree with Colbtech that it would be easier to just use named tabs. However, to modify his code, you can do this with ONE generic button and the text in cells.

    Use:
    Sheets(ActiveCell.Value).Select

    As long as the highlighted cell contains the name of an existing worksheet it will work.
  • Aug 8, 2006, 11:08 AM
    Wonder84lalala
    Cheech, how can I convert spreadsheets to web pages?
    Colbtech, I have a lot of tabs.
    Scott, do I use this code in Excel VBA?: Sheets(ActiveCell.Value).Select

    Thanks for your helps.
  • Aug 8, 2006, 11:37 AM
    ScottGem
    You can export worksheets to HTML. But that doesn't really give you want you want. I really don't think that was good advice. It wasn't technically incorrect, but I don't feel it was applicable to your needs.

    Yes, you would create one button and put that one line of code behind the button. The user then selects a cell, presses the button and it goes to that sheet.
  • Aug 9, 2006, 05:05 AM
    Wonder84lalala
    Thank you for your help guys, now I am becoming an Excel expert :D
  • Oct 12, 2006, 09:27 AM
    Mgaines
    Actually I think the best way for you to go is to create a user form with buttons for all of your tabs. Name the buttons the same name as your tabs. For each click for each btn the code would be: [sheet].activate

    This will bring that particular sheet to the front.
  • Oct 12, 2006, 09:30 AM
    ScottGem
    Quote:

    Originally Posted by Mgaines
    Actually I think the best way for you to go is to create a user form with buttons for all of your tabs. Name the buttons the same name as your tabs. For each click for each btn the code would be: [sheet].activate

    This will bring that particular sheet to the front.

    This is similar to a solution suggested a couple of months ago. But what advantage does that have over just selecting the worksheet tab?
  • Oct 12, 2006, 09:41 AM
    Mgaines
    Quote:

    Originally Posted by ScottGem
    This is similar to a solution suggested a couple of months ago. But what advantage does that have over just selecting the worksheet tab?

    I myself am working on a workbook that has 36 tabs. There is no way, that I am aware of, to cascade the tabs so that all are visible. If I opened my work book and needed to add to a sheet named December Closed Files 2006 (and there's January - December 2006 Case Table Tabs and January - December 2006 Open Files Tabs; totaling 36 tabs), it would be much easier to be able to click a button that takes me there from a user friendly form rather than scrolling through the tabs.
  • Oct 12, 2006, 10:24 AM
    ScottGem
    OK, that makes sense IF you have more tabs then can appear without scrolling. Of course, at that point, it might be better to move the data into a database.
  • Oct 12, 2006, 10:51 AM
    Mgaines
    Quote:

    Originally Posted by ScottGem
    OK, that makes sense IF you have more tabs then can appear without scrolling. Of course, at that point, it might be better to move the data into a database.

    This is true, but awfully troublesome and time consuming. Those small businesses mostly don't have the time or money for a project of this level. I assume if they did, Excel wouldn't have been their initial solution and a specific practice oriented database or a general database such as Access or Oracle would have been chosen and support outsourced.
  • Oct 12, 2006, 10:54 AM
    ScottGem
    I disagree. Access can be used on a number of different levels. A simple Access database can be setup in a matter of hours.
  • Dec 28, 2006, 04:33 PM
    joytnguyen
    Quote:

    Originally Posted by Wonder84lalala
    hi,

    I have many worksheets within the same file, what I really want is to have either a (link / go to option) from sheet1 to other sheets without using hyperlink because i want to send to others.

    Example: if I click on Apple from Sheet 1, I will go to Sheet2

    Sheet1 Sheet2 Sheet3 Sheet4
    Apple Apple Banana Orange
    Banana
    Orange


    thanks for your help,
    tuong

    Tuong, the best is to write macro... Joy
  • Dec 28, 2006, 04:36 PM
    joytnguyen
    Guys, the best is to write a macro... macro within excel, works really well
  • Dec 28, 2006, 06:00 PM
    ScottGem
    Quote:

    Originally Posted by joytnguyen
    Tuong, the best is to write macro....Joy

    First, this thread is several months old. Second, a macro was not necessary.
  • Dec 29, 2006, 08:28 AM
    Mgaines
    Macro's were the suggestion. See the above posts.

  • All times are GMT -7. The time now is 02:54 AM.