Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Excel forumla, read from another sheet and match text

  • 06-10-2015 07:06PM
    #1
    Closed Accounts Posts: 5,824 ✭✭✭


    Bit of a long shot asking.

    I'm using Excel, to create an expenses claim form.

    Sheet 1 is called details.

    It contains a list of places I visit, along with the distance travelled.

    Sheet 2 is called October.

    I write the name of the place I visit in Column A.

    I want, Column B to auto fill the distance of the place I write in Column A, by reading the info from Column B beside the matching location in column A in Sheet 1.

    Example.

    Sheet 1

    A1 Location A2 Distance
    House 1 28km
    House 2 15.5km

    Sheet 2

    I select from a drop down menu and pick the place i visit.

    A1 Location A2 Distance
    (menu) (auto fills based on the drop down menu and reads from sheet 1)

    Is this possible?

    Secondly, in Column C on Sheet 2, I want it to then multiply the value in Column B and multiply it by 2 and then multiply it by a value defined in another cell. Say sheet 1, Column C Row 3 for example.

    Once drop down menu is selected, it multiply's the value under A2 on sheet 2 by 2 and then by another value in a diff cell.

    Anyone handy with Excel able to confirm if this can be done, and secondly, anyone wanna try making it work? :)

    Thanks in advance!


Comments

  • Registered Users, Registered Users 2 Posts: 71,799 ✭✭✭✭Ted_YNWA


    On Sheet 1 input your location & distances like below.

    364782.png


    On Sheet 2, likewise.

    Vlookup works as =(cell_to_lookup,the_range,which_column_in_range,0)

    364783.png

    replace the randomCell with whatever extra calculation you need




    To create dropdown list:

    Go to Data -> Data Validation on the toolbar

    Select List in dropdown.
    In Source select the highlighed box & select the location range on Sheet1
    364784.png


  • Closed Accounts Posts: 5,824 ✭✭✭RoyalMarine


    Awesome! Thanks dude!!


  • Registered Users, Registered Users 2 Posts: 71,799 ✭✭✭✭Ted_YNWA


    Awesome! Thanks dude!!

    No problem :)


Advertisement