1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel 2016 cell calculation based on 2 different lists

Discussion in 'Business Applications' started by Ripperdan, Oct 1, 2017.

Thread Status:
Not open for further replies.
Advertisement
  1. Ripperdan

    Ripperdan Thread Starter

    Joined:
    Oct 28, 2009
    Messages:
    61
    First Name:
    Danny
    In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

    As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

    Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

    Tech Support Guy System Info Utility version 1.0.0.4
    OS Version: Microsoft Windows 8.1, 64 bit
    Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
    Processor Count: 8
    RAM: 12227 Mb
    Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
    Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
    Motherboard: ASUSTeK COMPUTER INC., K30AD_M31AD_M51AD_M32AD
    Antivirus: Webroot SecureAnywhere, Enabled and Updated
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    try
    http://skp.mvps.org/xl00002.htm

    as entered
    =IFERROR(INDEX('DATA Sheet'!$B$2:$K$11,MATCH('Mileage Log '!B8,'DATA Sheet'!$A$2:$A$11,0),MATCH('Mileage Log '!C8,'DATA Sheet'!$B$1:$K$1,0)),"")
     

    Attached Files:

  3. Ripperdan

    Ripperdan Thread Starter

    Joined:
    Oct 28, 2009
    Messages:
    61
    First Name:
    Danny
    Works perfect. Thank you very much.

    I hope I will be able to see how to modify it if and when the DATA Sheet needs to be modified.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    the link explains how it works
    so you should be ok
    if not , come back here
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1197240

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice