I have a spreadsheet that I want to track the visits of patients and the how
much I would owe the site for the visits. I have another spreadsheet that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit. The
cost for each visit varies. I have a spreadsheet that calculates the cost of
each visit and also the total cost depending on the number of visits. Say
patient 003 had completed Screening, Day 1 and Day 2, my second spreadsheet
calculates how much we would owe for the patient for the 3 visits. On my
first spreadsheet I have excel enter the dates of each visit by adding the
days to visit Day 1. At the end of the row I have a count of the number of
visits that have been completed. I would like to have excel enter the cost of
the number of visits. For example, if at the end of the row the patient had
completed 4 visits, I want excel to enter the cost of the 4 visits that has
been calculated on the second spreadsheet. There are 8 visits in all. What
type of formula could I use to achieve this. I want excel to look in say
cell F2, if there is a 4 in F2 how can I have excel enter the right cost for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is 5, then
enter $8,987.00 etc. Im not sure how to approach this. Any help would be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee

What a long question? Have you read Help to see how VLOOKUP works - I think
that answers your question. Happy to give more info if question gets
refined.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dee" wrote in message
...
I have a spreadsheet that I want to track the visits of patients and the
how
much I would owe the site for the visits. I have another spreadsheet that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit.
The
cost for each visit varies. I have a spreadsheet that calculates the cost
of
each visit and also the total cost depending on the number of visits. Say
patient 003 had completed Screening, Day 1 and Day 2, my second
spreadsheet
calculates how much we would owe for the patient for the 3 visits. On my
first spreadsheet I have excel enter the dates of each visit by adding the
days to visit Day 1. At the end of the row I have a count of the number of
visits that have been completed. I would like to have excel enter the cost
of
the number of visits. For example, if at the end of the row the patient
had
completed 4 visits, I want excel to enter the cost of the 4 visits that
has
been calculated on the second spreadsheet. There are 8 visits in all. What
type of formula could I use to achieve this. I want excel to look in say
cell F2, if there is a 4 in F2 how can I have excel enter the right cost
for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then
enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is 5,
then
enter $8,987.00 etc. I'm not sure how to approach this. Any help would be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee

I know it was a bit long lets try this.

Sheet 1
Subject Day 1 Day 2 Day 3 Total # Visits Total
Owed
001 07/17/05 07/18/05 2
002 07/15/05 1

Sheet 2
# of Visits Cost

1 $1880.00
2 $3987.00
3 $4353.00
4 $5850.00 etc.

I would like excel to look in value of Total # visits in sheet 1 and match
it to # visits in sheet 2 and add the amount for the # of visits in sheet 1
in the total owed column.
Does this make sense?

Best regards,

Dee

"Bernard Liengme" wrote:

What a long question? Have you read Help to see how VLOOKUP works - I think
that answers your question. Happy to give more info if question gets
refined.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dee" wrote in message
...
I have a spreadsheet that I want to track the visits of patients and the
how
much I would owe the site for the visits. I have another spreadsheet that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit.
The
cost for each visit varies. I have a spreadsheet that calculates the cost
of
each visit and also the total cost depending on the number of visits. Say
patient 003 had completed Screening, Day 1 and Day 2, my second
spreadsheet
calculates how much we would owe for the patient for the 3 visits. On my
first spreadsheet I have excel enter the dates of each visit by adding the
days to visit Day 1. At the end of the row I have a count of the number of
visits that have been completed. I would like to have excel enter the cost
of
the number of visits. For example, if at the end of the row the patient
had
completed 4 visits, I want excel to enter the cost of the 4 visits that
has
been calculated on the second spreadsheet. There are 8 visits in all. What
type of formula could I use to achieve this. I want excel to look in say
cell F2, if there is a 4 in F2 how can I have excel enter the right cost
for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then
enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is 5,
then
enter $8,987.00 etc. I'm not sure how to approach this. Any help would be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee

Hi Dee
You say in your original posts that there are up to 8 visits, so Iassume
your dates will be in columns B to H
In cell I2 of your Sheet1 enter
=COUNTIF(B2:H2"<") and this will give the total number of visits
in cell J2 of Sheet1 enter
=VLOOKUP(I2,'Sheet 2'!$A$2:$B$9,2,0)

--
Regards
Roger Govier
"Dee" wrote in message
...
I know it was a bit long lets try this.

Sheet 1
Subject Day 1 Day 2 Day 3 Total # Visits
Total
Owed
001 07/17/05 07/18/05 2
002 07/15/05 1

Sheet 2
# of Visits Cost

1 $1880.00
2 $3987.00
3 $4353.00
4 $5850.00 etc.

I would like excel to look in value of Total # visits in sheet 1 and match
it to # visits in sheet 2 and add the amount for the # of visits in sheet
1
in the total owed column.
Does this make sense?

Best regards,

Dee

"Bernard Liengme" wrote:

What a long question? Have you read Help to see how VLOOKUP works - I
think
that answers your question. Happy to give more info if question gets
refined.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dee" wrote in message
...
I have a spreadsheet that I want to track the visits of patients and the
how
much I would owe the site for the visits. I have another spreadsheet
that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit.
The
cost for each visit varies. I have a spreadsheet that calculates the
cost
of
each visit and also the total cost depending on the number of visits.
Say
patient 003 had completed Screening, Day 1 and Day 2, my second
spreadsheet
calculates how much we would owe for the patient for the 3 visits. On
my
first spreadsheet I have excel enter the dates of each visit by adding
the
days to visit Day 1. At the end of the row I have a count of the number
of
visits that have been completed. I would like to have excel enter the
cost
of
the number of visits. For example, if at the end of the row the patient
had
completed 4 visits, I want excel to enter the cost of the 4 visits that
has
been calculated on the second spreadsheet. There are 8 visits in all.
What
type of formula could I use to achieve this. I want excel to look in
say
cell F2, if there is a 4 in F2 how can I have excel enter the right
cost
for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then
enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is
5,
then
enter $8,987.00 etc. I'm not sure how to approach this. Any help would
be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee

Dear Roger,

It worked! Thank you so much for your help.

Best regards,

Dee

"Roger Govier" wrote:

Hi Dee
You say in your original posts that there are up to 8 visits, so Iassume
your dates will be in columns B to H
In cell I2 of your Sheet1 enter
=COUNTIF(B2:H2"<") and this will give the total number of visits
in cell J2 of Sheet1 enter
=VLOOKUP(I2,'Sheet 2'!$A$2:$B$9,2,0)

--
Regards
Roger Govier
"Dee" wrote in message
...
I know it was a bit long lets try this.

Sheet 1
Subject Day 1 Day 2 Day 3 Total # Visits
Total
Owed
001 07/17/05 07/18/05 2
002 07/15/05 1

Sheet 2
# of Visits Cost

1 $1880.00
2 $3987.00
3 $4353.00
4 $5850.00 etc.

I would like excel to look in value of Total # visits in sheet 1 and match
it to # visits in sheet 2 and add the amount for the # of visits in sheet
1
in the total owed column.
Does this make sense?

Best regards,

Dee

"Bernard Liengme" wrote:

What a long question? Have you read Help to see how VLOOKUP works - I
think
that answers your question. Happy to give more info if question gets
refined.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dee" wrote in message
...
I have a spreadsheet that I want to track the visits of patients and the
how
much I would owe the site for the visits. I have another spreadsheet
that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit.
The
cost for each visit varies. I have a spreadsheet that calculates the
cost
of
each visit and also the total cost depending on the number of visits.
Say
patient 003 had completed Screening, Day 1 and Day 2, my second
spreadsheet
calculates how much we would owe for the patient for the 3 visits. On
my
first spreadsheet I have excel enter the dates of each visit by adding
the
days to visit Day 1. At the end of the row I have a count of the number
of
visits that have been completed. I would like to have excel enter the
cost
of
the number of visits. For example, if at the end of the row the patient
had
completed 4 visits, I want excel to enter the cost of the 4 visits that
has
been calculated on the second spreadsheet. There are 8 visits in all.
What
type of formula could I use to achieve this. I want excel to look in
say
cell F2, if there is a 4 in F2 how can I have excel enter the right
cost
for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then
enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is
5,
then
enter $8,987.00 etc. I'm not sure how to approach this. Any help would
be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee

Hi Dee
You're more than welcome. Glad you have it sorted

--
Regards
Roger Govier
"Dee" wrote in message
...
Dear Roger,

It worked! Thank you so much for your help.

Best regards,

Dee

"Roger Govier" wrote:

Hi Dee
You say in your original posts that there are up to 8 visits, so Iassume
your dates will be in columns B to H
In cell I2 of your Sheet1 enter
=COUNTIF(B2:H2"<") and this will give the total number of visits
in cell J2 of Sheet1 enter
=VLOOKUP(I2,'Sheet 2'!$A$2:$B$9,2,0)

--
Regards
Roger Govier
"Dee" wrote in message
...
I know it was a bit long lets try this.

Sheet 1
Subject Day 1 Day 2 Day 3 Total # Visits
Total
Owed
001 07/17/05 07/18/05 2
002 07/15/05
1

Sheet 2
# of Visits Cost

1 $1880.00
2 $3987.00
3 $4353.00
4 $5850.00 etc.

I would like excel to look in value of Total # visits in sheet 1 and
match
it to # visits in sheet 2 and add the amount for the # of visits in
sheet
1
in the total owed column.
Does this make sense?

Best regards,

Dee

"Bernard Liengme" wrote:

What a long question? Have you read Help to see how VLOOKUP works - I
think
that answers your question. Happy to give more info if question gets
refined.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dee" wrote in message
...
I have a spreadsheet that I want to track the visits of patients and
the
how
much I would owe the site for the visits. I have another spreadsheet
that
works out the cost. per visit. For example, the patients would be
seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final
Visit.
The
cost for each visit varies. I have a spreadsheet that calculates the
cost
of
each visit and also the total cost depending on the number of
visits.
Say
patient 003 had completed Screening, Day 1 and Day 2, my second
spreadsheet
calculates how much we would owe for the patient for the 3 visits.
On
my
first spreadsheet I have excel enter the dates of each visit by
adding
the
days to visit Day 1. At the end of the row I have a count of the
number
of
visits that have been completed. I would like to have excel enter
the
cost
of
the number of visits. For example, if at the end of the row the
patient
had
completed 4 visits, I want excel to enter the cost of the 4 visits
that
has
been calculated on the second spreadsheet. There are 8 visits in
all.
What
type of formula could I use to achieve this. I want excel to look
in
say
cell F2, if there is a 4 in F2 how can I have excel enter the right
cost
for
4 visits in cell G2. Would I use an If, then function. If F2 is 4
then
enter
$4,567.99 which is calculated on the second spreadsheet and if F2
is
5,
then
enter $8,987.00 etc. I'm not sure how to approach this. Any help
would
be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee