Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Trying to set up a spreadsheet and have some rookie questions


  • Please log in to reply

#1
moondog830

moondog830

    Member

  • Member
  • PipPipPip
  • 598 posts

I want to create a sheet that has 3 columns

 

Date

 

City

 

Mileage

 

I would like to set it up so that if I type in a City name that it automatically puts in the correct mileage and that the mileage adds up in a TOTAL box at the end of the sheet.

 

Is any of this possible?

 

Mark


  • 0

Advertisements


#2
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts

Hello Moondog830,

 

Welcome to the forum!

 

What you are asking about is certainly possible, though I have a couple questions for you.

 

1. Do you have a specific list of Cities and related mileages?

2. How will this sheet be used? Weekly, Monthly, Annually?

 

Sincerely,

 

DM


  • 0

#3
moondog830

moondog830

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 598 posts

Hi DM,

 

1.   I do have a specific list of cities and related mileages.

 

2.   The list will be used annually for tax purposes. Although, I guess there are other uses I could use this for, once I learn the right way.

 

dog 


  • 0

#4
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts

Hello again,

 

I've saved a sample Excel 2010 workbook here:  LINK (you can download the file)

 

It provides some guidance and uses the following features:

  • Lists
  • Data Validation (used with the list to create the drop-down choices for the City)
  • VLOOKUP function
  • IF and ISERROR functions
  • SUM function

Take some time to go through it and feel free to ask additional questions.

 

DM


  • 0

#5
dbreeze

dbreeze

    Trusted Helper

  • Malware Removal
  • 2,184 posts

Nice workbook but shouldn't the data table in the List worksheet be a locked location?


  • 0

#6
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts

Hello dbreeze,

 

It certainly could be, though it sounds like the OP will be the only user of the workbook and locking down the list probably isn't necessary. 

 

If the workbook will be sent to several users then I typically add any lists to one sheet and then hide the sheet.  Most staff don't search out hidden sheets and I haven't had any issues with that structure. 

 

Like most things in Excel, there are several ways to accomplish what's needed. :geek:

 

Enjoy the rest of your day!

 

DM


  • 0

#7
dbreeze

dbreeze

    Trusted Helper

  • Malware Removal
  • 2,184 posts

I was referring to the actual cell range used in the vlookup.  The start and end points should be absolute not relative.  $A$1:$B$9 


  • 0

#8
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts

I was referring to the actual cell range used in the vlookup.  The start and end points should be absolute not relative.  $A$1:$B$9 

 

Ah, yes, they should...good catch.... :spoton:


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP