General discussion

  • Creator
    Topic
  • #2316217

    Link Excel record across sheets

    Locked

    by dennisbv ·

    I am cursed with an Excell report that was dumpped in my lap. Using anything but Excell is not an option. I have a first QTR report with truck maint, and I have second QTR. I have to do a Year to date. The problem is they have four locations with multiple departments and these trucks get moved between them. What I need to do is to be able bring a trucks information from the Qtr reports to the YTD no matter how the Qtr is sorted. I Have to follow the format of all of the qtrs and ytd in one work book. Can I link my cells by the trucks serial number so no mater what location or dept it is in it will show corectly on my YTD?

All Comments

  • Author
    Replies
    • #3380254

      Reply To: Link Excel record across sheets

      by sgt_shultz ·

      In reply to Link Excel record across sheets

      i don’t think so if i am following you. you can easily reference all the sheets in this workbook and others in your formulae. see excel help for the syntax…you need a lookup function to find where the serial number is currently living…let me see…

      • #3380250

        Reply To: Link Excel record across sheets

        by sgt_shultz ·

        In reply to Reply To: Link Excel record across sheets

        search help for look up. this is from my Excel 2002 help. hope is is of some use…

        Use the OFFSET and MATCH functions to do this task.

        Use this process when your data is in an external data range that you refresh each day. You know the price is in column B, but you don’t know how many rows of data the server will return, and first column isn’t sorted alphabetically.

        Worksheet example
        The example may be easier to understand if you copy it to a blank worksheet.

        How?

        Create a blank workbook or worksheet.
        Select the example in the Help topic. Do not select the row or column headers.

        Selecting an example from Help

        Press CTRL+C.
        In the worksheet, select cell A1, and press CTRL+V.
        To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

        1
        2
        3
        4
        5
        A B
        Product Count
        Bananas 38
        Oranges 25
        Apples 41
        Pears 40
        Formula Description (Result)
        =OFFSET(A1,MATCH(“Pears”,A2:A5, 0),1) Looks up Pears in column A and returns the value for Pears in column B ( 40).

        The formula uses the following arguments.

        A1: The upper left cell of the list, also called the starting cell.

        MATCH(“Pears”,A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.

        “Pears”: The value to find in the lookup column.

        A2:A5: The column for the MATCH function to search. Don’t include the starting cell in this range. Make sure the range allows for expansion of the list.

        1: The number of columns to the right of the starting cell to find the lookup value.

      • #3364423

        Reply To: Link Excel record across sheets

        by dennisbv ·

        In reply to Reply To: Link Excel record across sheets

        Poster rated this answer.

    • #3380157

      Reply To: Link Excel record across sheets

      by abarnes70 ·

      In reply to Link Excel record across sheets

      You can use the VLOOKUP function to search for the truck serial number. Use Insert/Function and lookup for VLOOKUP, and then click Help.

    • #3364421

      Reply To: Link Excel record across sheets

      by dennisbv ·

      In reply to Link Excel record across sheets

      This question was closed by the author

Viewing 2 reply threads