Results 1 to 10 of 10

Thread: Create an index

  1. #1
    Join Date
    Jun 2004
    Posts
    39

    Unanswered: Create an index

    Hi,
    how can we create an index in excel.
    i want to have the customer-codes and on clicking a customer code, i should get the orders of the customer. it can be in single-sheet or multiple sheets, anything is fine for me. but no macros or vba programming, as i'm already importing all th data from sqlserver to excel with vb code.
    no i want all the formula's or whatever it is to be already present in the excel file and i cant write the code again in excel.
    please help me.
    thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by madhavi28
    Hi,
    how can we create an index in excel.
    i want to have the customer-codes and on clicking a customer code, i should get the orders of the customer. it can be in single-sheet or multiple sheets, anything is fine for me. but no macros or vba programming, as i'm already importing all th data from sqlserver to excel with vb code.
    no i want all the formula's or whatever it is to be already present in the excel file and i cant write the code again in excel.
    please help me.
    thanks.
    Howdy.

    Do you already have the customer codes are coming from the import? If you need to pull out the unique names, then use Data > Filter > Advanced Filter, and be sure to copy to another location, and make sure that the Unique Values is checked. Then you can sort that list. Copy that to the sheet you want as an index.

    Without VBA, the easiest way is to hyperlink each cell that contains customer code. The hyperlink can be set to a specific area, or even a specific view. Need a little more detail on your set up to give any more advice.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jun 2004
    Posts
    39
    Yes, unique Customer codes are being imported in one sheet and other sheet has all details of purchases made by the customer. If I click the customer code then, it should take me to the purchases sheet, and on that particular customer only.
    If i create hyperlink i'm able to give for the entire sheet, not specific.
    I want it in specific, on clicking the hyperlink it should take me to the purchases of that customer directly.
    Thats the problem.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    The hyperlink can be specific:

    1. Select Insert from the menu
    2. Select Hyperlink
    3. Select Existing File or Web Page from the left hand side
    4. Either Browse for the file or see if it is in Recent Files
    5. Select Bookmark on the right side
    6. Choose either Cell Reference or Defined Names

    Each hyperlink will have to be done individually.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jun 2004
    Posts
    39
    There are about 3000 records and for each customer, i cant do this.
    can i create this hyperlink using vb code.

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Possibly try this (or modification of it):
    Code:
    Sub LinkSheetNames()
    
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
    
        i = 2
        n = ActiveWorkbook.Worksheets.Count
        For b = 1 To n
            If Worksheets(b).Name = "Table of Contents" Then
                Worksheets(b).Delete
            Else
                Worksheets.Add.Name = "Table of Contents"
                Range("A1") = "Table of Contents"
                GoTo Table
            End If
        Next
    
    Table:
        n = n + 1
        a = 2
        i = 2
        Do While i <= n
            Cells(a, 1).Select
            j = Worksheets(i).Name
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=j & "!A1", _
                                       TextToDisplay:=j
            a = a + 1
            i = i + 1
        Loop
    
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jun 2004
    Posts
    39
    it didnt work for me.
    what is subaddress in the command.

  8. #8
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    What you're asking to do would probably be easier to accomplish (especially as you said that you didn't want VBA) in MS Access. You can easily set up a query to return informaiton specific to each customer. In the query field for customer name, under criteria, just put [Enter Customer Name] to create an Input Box at run-time. In addition, with 3,000 records per customer, you'll max Excel's row limit with only 22 customers.

    You can easily import your SQL data into Access as well.

    Hope that helps,

    Smitty

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Thanks, Smitty.

    I realized I was at the edge of my experience in this question and invited Smitty to respond.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Jun 2004
    Posts
    39
    i think you smitty gave me a wrong suggestion.
    i already have queries and finally all the data should go in excel and there i give formulas for calculating the subtotals and chart etc., everythign using ms-access code.
    Ofcourse i resolved it.
    i created one sheet having all the details and added one more sheet only with the summarywiht customer codes and all the related invoices of the customer.
    then i get the invoice number's cell address in the detail sheet and add a hyperlink to the cell in the summary sheet.
    That's it.
    Anyhow thanks for the replies. Thanks Shades as you gave me an idea as to how to proceed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •