roundtrip mileage with one name entered in A1

[rkstaggers]

Would like to set up Excel to keep mileage to my Doctors appointments. Need
formula, function to do this. All I want to have to do is put my doctors name
in and have it put his address in and roundtrip mileage. Then the esay sum of
all trips. I can do that part.
So A1 I wold put Dr. name: Post, Greenberg, Hall, Adams etc. Out put would
be there address and predetermind mileage. Dr, Greenberg address 1234 Main
St, Denver, Colo. roudtrip mileage is 70, Dr. Adams 566 Jay Drive Colorado
Springs, Colo roundtrip mileage is 18, etc.

I know for you power users this will be a snap. I have nevr used Excel
before and never used text as the Input to output a number.

[davesexcel]

here's a good start

A1 to A5 enter these values

a
b
c
d
e
B1:B5 enter this
1
2
3
4
5
C1:C5 enter this
1a
2b
3c
4d
5e
place this formula in E1
=VLOOKUP(D1,A1:C5,2)
place this formula in F1
=VLOOKUP(D1,A1:C5,3)
Now in D1 enter any value that is in column A and see what happens

You can use column A as the list for the doctors names
Colmn B you can use for another, such as the doctors address
column C will be the milage,

Let me know if you cannot get this to work for your requirements

[rkstaggers]

After looking around it started to come back. I used Microsoft Works, but not
for many years. But I was using the IF in an formula and was going to be shut
down by the limit of 7 nests and would have had to do this for each output
that I needed.
But as expected someone would come up with a much esaier way. Thanks Dave, I
think this will work fine. What would our world be without people like you
helping others.
I would be reading a lot of the help files like I did 15 years ago when I
got my first computer, It didn't even have a hard drive.

"davesexcel" wrote:

>
> here's a good start
>
> A1 to A5 enter these values
>
> a
> b
> c
> d
> e
> B1:B5 enter this
> 1
> 2
> 3
> 4
> 5
> C1:C5 enter this
> 1a
> 2b
> 3c
> 4d
> 5e
> place this formula in E1
> =VLOOKUP(D1,A1:C5,2)
> place this formula in F1
> =VLOOKUP(D1,A1:C5,3)
> Now in D1 enter any value that is in column A and see what happens
>
> You can use column A as the list for the doctors names
> Colmn B you can use for another, such as the doctors address
> column C will be the milage,
>
> Let me know if you cannot get this to work for your requirements
>
>
>
> --
> davesexcel
> ------------------------------------------------------------------------
> davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
> View this thread: http://www.excelforum.com/showthread...hreadid=519191
>
>

[rkstaggers]

Dave,
Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
address and mileage. The mileage for that address and are correct, but not
correct for the Dr's name.

"rkstaggers" wrote:

> After looking around it started to come back. I used Microsoft Works, but not
> for many years. But I was using the IF in an formula and was going to be shut
> down by the limit of 7 nests and would have had to do this for each output
> that I needed.
> But as expected someone would come up with a much esaier way. Thanks Dave, I
> think this will work fine. What would our world be without people like you
> helping others.
> I would be reading a lot of the help files like I did 15 years ago when I
> got my first computer, It didn't even have a hard drive.
>
> "davesexcel" wrote:
>
> >
> > here's a good start
> >
> > A1 to A5 enter these values
> >
> > a
> > b
> > c
> > d
> > e
> > B1:B5 enter this
> > 1
> > 2
> > 3
> > 4
> > 5
> > C1:C5 enter this
> > 1a
> > 2b
> > 3c
> > 4d
> > 5e
> > place this formula in E1
> > =VLOOKUP(D1,A1:C5,2)
> > place this formula in F1
> > =VLOOKUP(D1,A1:C5,3)
> > Now in D1 enter any value that is in column A and see what happens
> >
> > You can use column A as the list for the doctors names
> > Colmn B you can use for another, such as the doctors address
> > column C will be the milage,
> >
> > Let me know if you cannot get this to work for your requirements
> >
> >
> >
> > --
> > davesexcel
> > ------------------------------------------------------------------------
> > davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
> > View this thread: http://www.excelforum.com/showthread...hreadid=519191
> >
> >

[JudithJubilee]

Hello rkstaggers,

I hope Dave doesn't mind me jumping in but this caught my eye!

When you copy the formula down the columns it is changing the cell references
that it is refering to. Each cell in columns D and E need to look at the same
table of Doctor's information. As Dave said he was showing you how the
formula worked to start you off - you just need to tweak where you are
putting data.

You should really have the table of info somewhere else on the worksheet. Cut
and Paste the Doctor table (A1:C5) and put it either on a different worksheet
or at the top right of the sheet you are on. Then you can use column A to
enter the Dr names, B for the address and C for the mileage.

For the formula to work you want all the cells in B and C to refer to the
same set of cells. The best way to do this is to give the table cells a Range
Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
allowed in the name).

Now in Column B use the formula Dave gave you but with the Range name instead
of refs:

=VLOOKUP(A1,DoctorInfo,2)

In C

=VLOOKUP(A1,DoctorInfo,3)

You will now be able to copy this down.

Post back if you have any problems.

Judith


rkstaggers wrote:
>Dave,
>Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
>However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
>address and mileage. The mileage for that address and are correct, but not
>correct for the Dr's name.
>
>> After looking around it started to come back. I used Microsoft Works, but not
>> for many years. But I was using the IF in an formula and was going to be shut
>[quoted text clipped - 39 lines]
>> > Let me know if you cannot get this to work for your requirements
>> >

[rkstaggers]

I changed them to look at only A:1 to A:5 and same for colum B:1 toB:5. It
still brings up the wrong address and miles. I will move the info to another
sheet, but need to get this fixed first. D:1 works with all five Dr's names.

"JudithJubilee" wrote:

> Hello rkstaggers,
>
> I hope Dave doesn't mind me jumping in but this caught my eye!
>
> When you copy the formula down the columns it is changing the cell references
> that it is refering to. Each cell in columns D and E need to look at the same
> table of Doctor's information. As Dave said he was showing you how the
> formula worked to start you off - you just need to tweak where you are
> putting data.
>
> You should really have the table of info somewhere else on the worksheet. Cut
> and Paste the Doctor table (A1:C5) and put it either on a different worksheet
> or at the top right of the sheet you are on. Then you can use column A to
> enter the Dr names, B for the address and C for the mileage.
>
> For the formula to work you want all the cells in B and C to refer to the
> same set of cells. The best way to do this is to give the table cells a Range
> Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
> F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
> allowed in the name).
>
> Now in Column B use the formula Dave gave you but with the Range name instead
> of refs:
>
> =VLOOKUP(A1,DoctorInfo,2)
>
> In C
>
> =VLOOKUP(A1,DoctorInfo,3)
>
> You will now be able to copy this down.
>
> Post back if you have any problems.
>
> Judith
>
>
> rkstaggers wrote:
> >Dave,
> >Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
> >However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
> >address and mileage. The mileage for that address and are correct, but not
> >correct for the Dr's name.
> >
> >> After looking around it started to come back. I used Microsoft Works, but not
> >> for many years. But I was using the IF in an formula and was going to be shut
> >[quoted text clipped - 39 lines]
> >> > Let me know if you cannot get this to work for your requirements
> >> >
>

[JudithJubilee]

Hello rkstaggers,

Can you copy and paste the formula you have in cell E1 (the first VLOOKUP)
into a message and I'll have a look.

Judith

"rkstaggers" wrote:

> I changed them to look at only A:1 to A:5 and same for colum B:1 toB:5. It
> still brings up the wrong address and miles. I will move the info to another
> sheet, but need to get this fixed first. D:1 works with all five Dr's names.
>
> "JudithJubilee" wrote:
>
> > Hello rkstaggers,
> >
> > I hope Dave doesn't mind me jumping in but this caught my eye!
> >
> > When you copy the formula down the columns it is changing the cell references
> > that it is refering to. Each cell in columns D and E need to look at the same
> > table of Doctor's information. As Dave said he was showing you how the
> > formula worked to start you off - you just need to tweak where you are
> > putting data.
> >
> > You should really have the table of info somewhere else on the worksheet. Cut
> > and Paste the Doctor table (A1:C5) and put it either on a different worksheet
> > or at the top right of the sheet you are on. Then you can use column A to
> > enter the Dr names, B for the address and C for the mileage.
> >
> > For the formula to work you want all the cells in B and C to refer to the
> > same set of cells. The best way to do this is to give the table cells a Range
> > Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
> > F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
> > allowed in the name).
> >
> > Now in Column B use the formula Dave gave you but with the Range name instead
> > of refs:
> >
> > =VLOOKUP(A1,DoctorInfo,2)
> >
> > In C
> >
> > =VLOOKUP(A1,DoctorInfo,3)
> >
> > You will now be able to copy this down.
> >
> > Post back if you have any problems.
> >
> > Judith
> >
> >
> > rkstaggers wrote:
> > >Dave,
> > >Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
> > >However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
> > >address and mileage. The mileage for that address and are correct, but not
> > >correct for the Dr's name.
> > >
> > >> After looking around it started to come back. I used Microsoft Works, but not
> > >> for many years. But I was using the IF in an formula and was going to be shut
> > >[quoted text clipped - 39 lines]
> > >> > Let me know if you cannot get this to work for your requirements
> > >> >
> >

[JudithJubilee]

Also try adding FALSE to both VLOOKUPs

=VLOOKUP(A1,DoctorInfo,2,FALSE)

Judith
--
Hope this helps


"JudithJubilee" wrote:

> Hello rkstaggers,
>
> Can you copy and paste the formula you have in cell E1 (the first VLOOKUP)
> into a message and I'll have a look.
>
> Judith
>
> "rkstaggers" wrote:
>
> > I changed them to look at only A:1 to A:5 and same for colum B:1 toB:5. It
> > still brings up the wrong address and miles. I will move the info to another
> > sheet, but need to get this fixed first. D:1 works with all five Dr's names.
> >
> > "JudithJubilee" wrote:
> >
> > > Hello rkstaggers,
> > >
> > > I hope Dave doesn't mind me jumping in but this caught my eye!
> > >
> > > When you copy the formula down the columns it is changing the cell references
> > > that it is refering to. Each cell in columns D and E need to look at the same
> > > table of Doctor's information. As Dave said he was showing you how the
> > > formula worked to start you off - you just need to tweak where you are
> > > putting data.
> > >
> > > You should really have the table of info somewhere else on the worksheet. Cut
> > > and Paste the Doctor table (A1:C5) and put it either on a different worksheet
> > > or at the top right of the sheet you are on. Then you can use column A to
> > > enter the Dr names, B for the address and C for the mileage.
> > >
> > > For the formula to work you want all the cells in B and C to refer to the
> > > same set of cells. The best way to do this is to give the table cells a Range
> > > Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
> > > F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
> > > allowed in the name).
> > >
> > > Now in Column B use the formula Dave gave you but with the Range name instead
> > > of refs:
> > >
> > > =VLOOKUP(A1,DoctorInfo,2)
> > >
> > > In C
> > >
> > > =VLOOKUP(A1,DoctorInfo,3)
> > >
> > > You will now be able to copy this down.
> > >
> > > Post back if you have any problems.
> > >
> > > Judith
> > >
> > >
> > > rkstaggers wrote:
> > > >Dave,
> > > >Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
> > > >However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
> > > >address and mileage. The mileage for that address and are correct, but not
> > > >correct for the Dr's name.
> > > >
> > > >> After looking around it started to come back. I used Microsoft Works, but not
> > > >> for many years. But I was using the IF in an formula and was going to be shut
> > > >[quoted text clipped - 39 lines]
> > > >> > Let me know if you cannot get this to work for your requirements
> > > >> >
> > >

[rkstaggers]

place this formula in E1
=VLOOKUP(D1,A1:C5,2)
place this formula in F1
=VLOOKUP(D1,A1:C5,3)
in E:2 I put the same
and F:2 the same
so that it refers only to those A:1toC:5.

"JudithJubilee" wrote:

> Hello rkstaggers,
>
> Can you copy and paste the formula you have in cell E1 (the first VLOOKUP)
> into a message and I'll have a look.
>
> Judith
>
> "rkstaggers" wrote:
>
> > I changed them to look at only A:1 to A:5 and same for colum B:1 toB:5. It
> > still brings up the wrong address and miles. I will move the info to another
> > sheet, but need to get this fixed first. D:1 works with all five Dr's names.
> >
> > "JudithJubilee" wrote:
> >
> > > Hello rkstaggers,
> > >
> > > I hope Dave doesn't mind me jumping in but this caught my eye!
> > >
> > > When you copy the formula down the columns it is changing the cell references
> > > that it is refering to. Each cell in columns D and E need to look at the same
> > > table of Doctor's information. As Dave said he was showing you how the
> > > formula worked to start you off - you just need to tweak where you are
> > > putting data.
> > >
> > > You should really have the table of info somewhere else on the worksheet. Cut
> > > and Paste the Doctor table (A1:C5) and put it either on a different worksheet
> > > or at the top right of the sheet you are on. Then you can use column A to
> > > enter the Dr names, B for the address and C for the mileage.
> > >
> > > For the formula to work you want all the cells in B and C to refer to the
> > > same set of cells. The best way to do this is to give the table cells a Range
> > > Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
> > > F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
> > > allowed in the name).
> > >
> > > Now in Column B use the formula Dave gave you but with the Range name instead
> > > of refs:
> > >
> > > =VLOOKUP(A1,DoctorInfo,2)
> > >
> > > In C
> > >
> > > =VLOOKUP(A1,DoctorInfo,3)
> > >
> > > You will now be able to copy this down.
> > >
> > > Post back if you have any problems.
> > >
> > > Judith
> > >
> > >
> > > rkstaggers wrote:
> > > >Dave,
> > > >Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
> > > >However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
> > > >address and mileage. The mileage for that address and are correct, but not
> > > >correct for the Dr's name.
> > > >
> > > >> After looking around it started to come back. I used Microsoft Works, but not
> > > >> for many years. But I was using the IF in an formula and was going to be shut
> > > >[quoted text clipped - 39 lines]
> > > >> > Let me know if you cannot get this to work for your requirements
> > > >> >
> > >