+ Reply to Thread
Results 1 to 12 of 12

Managing worksheets in excel

  1. #1
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    445

    Managing worksheets in excel

    I have more than ten worksheets. The problem is, I take a lot of time looking for a worksheet every time when I want to work on a certain worksheet. Is it possible to group worksheets and leave one worksheet appearing and when I want to work on a certain worksheet I just click on the group and it appears. If it is possible please help me. I want excel formula only not VBA. I have attached excel sample sheet which explains better what I want.

    Thank you.

    Sunboy
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Managing worksheets in excel

    Hi Sunboy,
    if you want only excel and not VBA, the only efficient solution will be Power Query, this is a user-friendly User-interface based data transformation tool, do you have Excel 2016, it is under Data > get and transform, for you 10 sheets or more, just need to create a new workbook > blank query > use a simple command

    Excel.Workbook(File.Contents("d:\............\ABC.xlsx),null, true)

    or there is another method to select all the worksheets in one good and transform using Append as new (combine all the same structures worksheets into one)

    Not sure MS Excel 2010/2013 Add-in still available in the internet for you to download
    Christopher Yap

  3. #3
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    445

    Re: Managing worksheets in excel

    Hi Bluesky63

    I have tried to do it through Power Query but I got confused. if it is possible you may give me step by step information so that I get lost because there many options on Power query.


    Thank you

  4. #4
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    445

    Re: Managing worksheets in excel

    Hi Bluesky63

    I have tried to do it through Power Query but I got confused. if it is possible you may give me step by step information so that I don't get lost because there many options on Power query.


    Thank you

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Managing worksheets in excel

    Perhaps use the Excel's built in pop-up sheet index ?

  6. #6
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Managing worksheets in excel

    sunboy, please attach sample data workbook with few worksheets (same table structure), then I can combine for you with explaination

  7. #7
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Managing worksheets in excel

    Hi Sunboy, I prepared for you a quick, sample with 3 worksheets 1,2,3, each of them same structure and field headers, each this workbook in a folder and note down the folder path (d:\downloads\sample1.xlsx

    goto Data >new query > from other sources > blank query

    = Excel.Workbook(File.Contents("C:\Users\JO_HOME\Downloads\Test\sample1.xlsx"),null,true)

    Delete all column except Data, expand the table

    There will be several repeated header, you need only one, goto home > click on use first row as headers
    click on Field 1, check on Field 1 to filter off the remainder extra headers

    Home > Close and load to

    The combine report is done !! (see attached Combine_Several_Sheets.xlsx, sample1.xls is the source file
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    445

    Re: Managing worksheets in excel

    Hi Bluesky63

    Please find the sample data workbook
    Attached Files Attached Files

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Managing worksheets in excel

    Create hyperlinks manually or https://www.ozgrid.com/VBA/sheet-index.htm

  10. #10
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Managing worksheets in excel

    Hi Sunboy, what you want is a navigation list that can allow you to click on the particular worksheet rather than scrolling, thought you want to combine it

    Create a list of all the worksheets you have, each of it hyperlink it, and each of the worksheet create a back for you to click back the list

    or you can right click the left bottom inverted triangle area and select the list
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    445

    Re: Managing worksheets in excel

    Thank you all for your help.

    Regards,

    Sunboy

  12. #12
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,442

    Re: Managing worksheets in excel

    Quote Originally Posted by bluesky63 View Post
    Hi Sunboy, what you want is a navigation list that can allow you to click on the particular worksheet rather than scrolling, thought you want to combine it

    Create a list of all the worksheets you have, each of it hyperlink it, and each of the worksheet create a back for you to click back the list

    or you can right click the left bottom inverted triangle area and select the list
    @sunboy
    Same as my posts #9 and #5....
    Please read ALL the answers you receive

+ 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. Managing Data on excel
    By S2000 in forum Excel General
    Replies: 17
    Last Post: 03-29-2014, 05:41 PM
  2. Managing expenses with excel
    By dmcky in forum Excel General
    Replies: 5
    Last Post: 11-30-2013, 05:51 PM
  3. PDF managing from Excel macro
    By DGL in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 11:14 AM
  4. managing inventory with excel
    By BBLANCOBRNX in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-19-2012, 05:05 PM
  5. Managing Team In Excel
    By michellecairns in forum Excel General
    Replies: 4
    Last Post: 12-15-2011, 01:33 PM
  6. Managing data between worksheets
    By Drickmt in forum Excel General
    Replies: 1
    Last Post: 04-06-2008, 02:26 PM
  7. [SOLVED] Managing Excel from VB^
    By Uwe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2006, 02:45 PM

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