none
how to make a page set up visible by scrolling up or down RRS feed

  • Question

  • Hi,

    I have to use nearly 150 pages, 12-13 for each month. They take data from each other and do caluculations. If I use 150 worksheets in a workbook it will be very slow. I want to make 12 pages on one worksheet to enable work faster. I will need to use only 15 worksheets or so. Now I want is that when I activate worksheet, particular page should be visible, rest of the sheet area should scroll down or up, left or right and hidden from view. Only manually be visible if desired so. This to be acieved for all worksheets so that all worksheets show present months page on selection of that woeksheet.

    I couldn't make this possible, trying for long.

    Please help any vba,

    regards


    Wednesday, July 20, 2016 5:52 AM

Answers

  • So my point is, move all the data onto one sheet, then filter by date and whatever it is that causes you to put data on different sheets. If you do that, all the data is in one place and the user just needs to filter to get the subset they want; no scrolling, no zillion tabs, bingo-bango-bongo.

    If the filtering is consistent and predictable, you can do it with button driven macros. For example, I have a timesheet I manage where I enter tasks completed each day I work. I only want the sheet to display 'today' most of the time so I have a button that forces the table to set the filter to 'today' and poof, thousands of rows disappear and I see just the stuff I care about. The table is the data source for a pivot table that sums up hours for tasks by one of the column values so the button refreshes the pivot table at the same time. Data one place, reporting somewhere else, updates done by button.

    Simplify your data, use table structures so new data expands the defined range name automagically, filter as appropriate, and separate data from reports. It's not the only way to design systems but I've found it allows me to spend less time building and changing and changing and changing again weird user driven, highly customized designs and focus on building new stuff. Be smart, be lazy!

    • Marked as answer by David_JunFeng Wednesday, July 27, 2016 9:53 AM
    Wednesday, July 20, 2016 2:25 PM
  • >>> If I need other month's data I simply scroll up or down as desired. Suppose A1 to Z50 is page for January A51 to Z100 for February and likewise, A301 to Z350 will be for July. I open worksheet 

    today, it must scroll down to show A301 to Z350, Which I want to name as page JLMPR1. If I need to see June page, I simply scroll up.<<<

    According to your description, you could outline (group) data in a worksheet. You could use record macro to get VBA code when you manually create an outline of rows.

    For more information, click here to refer about Outline (group) data in a worksheet

    • Marked as answer by David_JunFeng Wednesday, July 27, 2016 9:53 AM
    Thursday, July 21, 2016 4:32 AM

All replies

  • I didn't understand everything you wrote.

    Have you tried to freeze panes or split columns/rows? Creating a new View?


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, July 20, 2016 12:17 PM
  • Your design and question are not totally clear but I think I have an inkling of what's up.

    You have tons of data that is somehow linked to dates/months/something and you want a way for the user to magically see some subset of the data when they open a specific sheet. Your current design is highly complex and uses tons of worksheet tabs and you want to simplify.

    If I'm even I the ball park, then your design needs to be rethought. I have a huge bias for creating sheets that are data-centric rather than human friendly, then build reporting structures/methods to generate the pretty stuff everyone wants.

    For example, I'd build a sheet with simple rows/columns, format the data as a table, and then use flters and/or pivot tables for 'reporting'. Can you organize the data so the system provides a way to filter or pivot the data into what the user needs to see? There was a post recently about configuring Excel to auto filter on the 'current month' when the file was opened so if that structure concept works, you have code elsewhere that shows you how to get what you need.

    Perhaps you store the data in Excel and then use something like Access to generate pretty reports.

    The other recommendation I have is to work backward when doing the design; start with the outputs/reports you need, then figure out what data structures and methods give you those outputs.

    Wednesday, July 20, 2016 12:49 PM
  • Hi Felipe Costa Gualberto,

    Thank you for reply.

    regards
    Wednesday, July 20, 2016 2:13 PM
  • Hi  Dogubob ,

    Thanks,

    It is not much complex what I want. I simply want worksheet to scroll down to present months page on worksheet automaticlly. If I need other month's data I simply scroll up or down as desired. Suppose A1 to Z50 is page for January A51 to Z100 for February and likewise, A301 to Z350 will be for July. I open worksheet today, it must scroll down to show A301 to Z350, Which I want to name as page JLMPR1. If I need to see June page, I simply scroll up. Hence all worksheets do the same thing.

    Regards.
    Wednesday, July 20, 2016 2:15 PM
  • So my point is, move all the data onto one sheet, then filter by date and whatever it is that causes you to put data on different sheets. If you do that, all the data is in one place and the user just needs to filter to get the subset they want; no scrolling, no zillion tabs, bingo-bango-bongo.

    If the filtering is consistent and predictable, you can do it with button driven macros. For example, I have a timesheet I manage where I enter tasks completed each day I work. I only want the sheet to display 'today' most of the time so I have a button that forces the table to set the filter to 'today' and poof, thousands of rows disappear and I see just the stuff I care about. The table is the data source for a pivot table that sums up hours for tasks by one of the column values so the button refreshes the pivot table at the same time. Data one place, reporting somewhere else, updates done by button.

    Simplify your data, use table structures so new data expands the defined range name automagically, filter as appropriate, and separate data from reports. It's not the only way to design systems but I've found it allows me to spend less time building and changing and changing and changing again weird user driven, highly customized designs and focus on building new stuff. Be smart, be lazy!

    • Marked as answer by David_JunFeng Wednesday, July 27, 2016 9:53 AM
    Wednesday, July 20, 2016 2:25 PM
  • >>> If I need other month's data I simply scroll up or down as desired. Suppose A1 to Z50 is page for January A51 to Z100 for February and likewise, A301 to Z350 will be for July. I open worksheet 

    today, it must scroll down to show A301 to Z350, Which I want to name as page JLMPR1. If I need to see June page, I simply scroll up.<<<

    According to your description, you could outline (group) data in a worksheet. You could use record macro to get VBA code when you manually create an outline of rows.

    For more information, click here to refer about Outline (group) data in a worksheet

    • Marked as answer by David_JunFeng Wednesday, July 27, 2016 9:53 AM
    Thursday, July 21, 2016 4:32 AM