+ Reply to Thread
Results 1 to 2 of 2

Need to sort an INDEX page in workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    10-29-2018
    Location
    La Grange, Texas
    MS-Off Ver
    365
    Posts
    12

    Need to sort an INDEX page in workbook

    I have a sheet named "INDEX" in my workbook which was created with a VBA macro for tabs 1 - 100. They are sequentially names L1 - L100. The index tab has columns where the "NAME" (taken from cell I2 in each sheet) is in column A and the cells with the index links (created with the macro while all sheets were blank) are in column B. The 'name' column automatically grabs its data from a "LAST NAME" cell in the same location in each worksheet. The formula used was " =INDIRECT("L"&(ROW()-1)+25*(COLUMN()-1)/3&"!I2") " On the index page, I have the links separated into 4 columns of 25 links each.

    The idea was that when ready to enter a new client, one could go to the INDEX tab and just select the next link with an empty name cell next to it. Once all the data is entered, then the last name in " I2 " of that sheet will show up next to the index link you just selected - and that works perfectly.

    I thought I would be able to sort these cells alphabetically to account for adding new clients....but it just doesn't work any way I've tried it. Can anyone point me in a direction to create an index for 100 tabs wherein I can re-sort by names alphabetically as new clients are added ?????

    Thanks in advance for your help !!!!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Need to sort an INDEX page in workbook

    The issue is you use the ROW() function to generate a number 1 to 100 depending on the location of the formula. So when you sort, the formula location changes, the formula recalculate based on its new location, and changes value.

    A simple solution is to have a column filled with constants 1 to 100 and reference that column in the formula. In this example, column Z has values 1 to 100
    =INDIRECT("L"&(Z1-1)+25*(COLUMN()-1)/3&"!I2")

    Now when you sort, include column Z in the sort range. Column Z will follow each formula and not cause it to recalculate with a different value.

    You may have to tweek the formula as I'm not sure of your layout. The idea is to replace the ROW() function with a cell reference that contains a constant.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ 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. [SOLVED] vba code to create an "index" page which lists every worksheet in the workbook
    By trenzalore888 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2016, 01:14 PM
  2. Fill in page from another page in workbook
    By Nightstouch in forum Excel General
    Replies: 6
    Last Post: 01-02-2014, 05:23 PM
  3. Replies: 6
    Last Post: 11-17-2013, 01:40 PM
  4. Replies: 2
    Last Post: 11-21-2012, 12:02 PM
  5. Sort Page Field With VBA
    By delecto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2009, 08:34 PM
  6. Sort page by Name
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2009, 08:06 PM
  7. How can I sort a protected page
    By AK 47 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2005, 07:07 AM

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