Results 1 to 4 of 4

lookup data from another worksheet

This is a discussion on lookup data from another worksheet within the Excel Questions forums, part of the Question Forums category; Greetings, I have a workbook containing various vehicle data. On one of these sheets (called Report) I show some summaries ...

  1. #1
    New Member
    Join Date
    Feb 2012
    Location
    Australia
    Posts
    3

    Question lookup data from another worksheet

    Greetings,

    I have a workbook containing various vehicle data. On one of these sheets (called Report) I show some summaries and visual representations of the data contained in the workbook, for printing purposes. Each sheet in the workbook contains vehicle registration numbers in one of the columns.

    So in the Report sheet, in column P, I wish to display a number which corresponds to last months fuel costs, which I'd like to be obtained from a sheet called Fuel.



    (currently the number is just copy+pasted, this is too tedious of a process to be continued however)

    In the Fuel sheet, starting from column F, dollar amounts are entered for each registration number for the month.



    So the row in the Fuel sheet will vary (it contains vehicles which are not on the Report sheet) but to get the right one I'd like to use the vehicle registration number obtained from the Report sheet in column B. The registration numbers in the Fuel sheet are in column D. The column to obtain the figure from would be the last column in the corresponding row which contains data.

    The ordering of the vehicles (the rows) on the Report sheet may vary but the rows on the other sheets will likely just remain in their current order - note that the rows will not correspond to each other.

    I can use LOOKUP to obtain the number, but it's not very dynamic and if I move the rows around things tend to get muddled. Hence why I'd like it to use the registration numbers to be more elegant.
    I am pretty sure I need to use VLOOKUP in some way, I suspect I need to nest some formulas to get this working perhaps? I would appreciate some guidance in this regard. I am using Excel 2010. Also I should mention I don't mind if the solution is via VBA or other macro.


    Thank you,
    Charles

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,391

    Default Re: lookup data from another worksheet

    Welcome to the Board!

    Unfortunately, I can't read your images all that well. Take a look at the link in my sig, which will let you post shots of your sheets that others can copy right into Excel to get you the right formula.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    New Member
    Join Date
    Feb 2012
    Location
    Australia
    Posts
    3

    Question Re: lookup data from another worksheet

    Thanks for the welcome! I've often used this forum as a resource and hopefully I'll be able to contribute to it in the future as well.

    I have installed the Add-In and it appears to be working alright... let's give it a try...


    Here is the a couple lines from the "Report" sheet as mentioned above:

    Excel 2010
    ABCDEFGHIJKLMNOPQR
    2Current LeaseMonthlyTotal MonthsOdometerReadingLast Month6 monthProjected
    3RegistrationMakeModelDescriptionDriver or SiteCommencedFinishTermKmsRentalover Leaseto EndReadingDateFuel Cost1TrendFinal kms2
    41SblahKNissanXtrailwagonSomeone3/12/20102/12/20122480000$ 1,582.26 $ 37,974.24 104614230/12/2011$ 448.20 85928
    52SblahMHyundaiix35wagonSomeoneElse15/09/201114/09/20132460000$ 801.78 $ 19,242.72 19934530/12/2011$ 438.58 64358

    Report




    And here are some lines from the Fuel sheet:

    Excel 2010
    ABCDEFGHIJKLMNO
    2TotalRegistrationCard No.Full #julyaugustseptemberoctobernovemberdecemberjanuaryfebruary
    3$ 4,501.81 1SblahK1234560012345$ - $ - $ - $ - $ - $ - $ 258.81 $ 432.52
    4$ 3,017.44 2SblahM5432160054321$ - $ - $ - $ 206.86 $ 17.84 $ 220.70 $ 170.11 $ 165.37

    Fuel

    I hope that makes it clearer. In the Fuel sheet there is currently data out to column Z and next month column AA will be filled in.


    Thank you,
    Charles

  4. #4
    New Member
    Join Date
    Feb 2012
    Location
    Australia
    Posts
    3

    Default Re: lookup data from another worksheet

    So... any advice out there? I will keep looking in the meantime I suppose!

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
  •  


DMCA.com