Results 1 to 2 of 2

Thread: Getting value from a different tab (How to make my current formula drag down?)

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Florida
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Getting value from a different tab (How to make my current formula drag down?)

    Hello!

    I made a formula to display the latest value entered in a column on a different tab.


    In the column on “Sheet2,” an employee enters the new amount of mileage driven in the rented car once it’s returned (see example below. Date and Mileage are two different columns):



    • “Date”..................... “Mileage”
    • 01/01/2019............... 123,456
    • 01/05/2019 ...............123,557
    • 01/27/2019 ...............124,896

    On the “Sheet1” page it displays the most recent mileage count ("124,896") using the formula below:

    =LOOKUP(2,1/(Sheet2!H2:H10000<>""),Sheet2!H2:H10000)

    The thing is, I have 5 different sheets with the same two columns as above and I need to use this formula to get the mileage values from all 5 sheets. As of now I am entering it manually in each cell to say "Sheet3!" or "Sheet4!" to gather the data.



    Is there a way I can create a drag down formula instead of manually entering the different sheet names?

    Thanks!

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Getting value from a different tab (How to make my current formula drag down?)

    If you have a list of your sheet names, you can use INDIRECT like this:

    NO
    1Sheet23
    2Sheet355
    3Sheet4222
    4Sheet56666

    Sheet1



    Worksheet Formulas
    CellFormula
    O1=LOOKUP(2,1/(INDIRECT("'"&N1&"'!H2:H10000")<>""),INDIRECT("'"&N1&"'!H2:H10000"))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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