I am helping set up a spreadsheet for a physical therapy center. One of the
Excel 2002 worksheets is for their invoices. There are about 33 current
procedures/products, but 3 different prices depending on the means the
procedures/products are paid by. On SHEET 1 is the layout of the invoice
itself with all calculations, and on SHEET 2 is a list of the 33
procedures/products currently offered. The list (range of cells) has been
named and incorporated into a drop-down list on SHEET 1. Now here is the
question:

How can I set this up so that when I select one of the 33
procedures/products from the drop-down list in a cell, it will allow me in
the next column to select (from another drop-down list) 1 of the 3 means
(Cash, Medicare, Insurance Provider, etc) by wich to charge the client by.
This is because the price is different depending on which of the 3 is chosen.
Is this possible? If so, please, the simplest way to do this.

I have further questions I would like to post based on this same project,
which include an easy and efficient way to incorporate these Excel Sheets
into a database, but still keeping all the formatting, calculations, cells,
and the actual look of the way it is designed graphically.
--
In God''s Harmony

Take a look here, she 'splains it pretty well.

HTH
Regards,
Howard

"Roddy" wrote in message
...
I am helping set up a spreadsheet for a physical therapy center. One of the
Excel 2002 worksheets is for their invoices. There are about 33 current
procedures/products, but 3 different prices depending on the means the
procedures/products are paid by. On SHEET 1 is the layout of the invoice
itself with all calculations, and on SHEET 2 is a list of the 33
procedures/products currently offered. The list (range of cells) has been
named and incorporated into a drop-down list on SHEET 1. Now here is the
question:

How can I set this up so that when I select one of the 33
procedures/products from the drop-down list in a cell, it will allow me in
the next column to select (from another drop-down list) 1 of the 3 means
(Cash, Medicare, Insurance Provider, etc) by wich to charge the client
by.
This is because the price is different depending on which of the 3 is
chosen.
Is this possible? If so, please, the simplest way to do this.

I have further questions I would like to post based on this same project,
which include an easy and efficient way to incorporate these Excel Sheets
into a database, but still keeping all the formatting, calculations,
cells,
and the actual look of the way it is designed graphically.
--
In God''s Harmony

Take a look where? I don't see a link or reference in your response.
--
In God''s Harmony


"L. Howard Kittle" wrote:

Take a look here, she 'splains it pretty well.

HTH
Regards,
Howard

"Roddy" wrote in message
...
I am helping set up a spreadsheet for a physical therapy center. One of the
Excel 2002 worksheets is for their invoices. There are about 33 current
procedures/products, but 3 different prices depending on the means the
procedures/products are paid by. On SHEET 1 is the layout of the invoice
itself with all calculations, and on SHEET 2 is a list of the 33
procedures/products currently offered. The list (range of cells) has been
named and incorporated into a drop-down list on SHEET 1. Now here is the
question:

How can I set this up so that when I select one of the 33
procedures/products from the drop-down list in a cell, it will allow me in
the next column to select (from another drop-down list) 1 of the 3 means
(Cash, Medicare, Insurance Provider, etc) by wich to charge the client
by.
This is because the price is different depending on which of the 3 is
chosen.
Is this possible? If so, please, the simplest way to do this.

I have further questions I would like to post based on this same project,
which include an easy and efficient way to incorporate these Excel Sheets
into a database, but still keeping all the formatting, calculations,
cells,
and the actual look of the way it is designed graphically.
--
In God''s Harmony

There are instructions for dependent data validation he

http://www.contextures.com/xlDataVal02.html

Roddy wrote:
Take a look where? I don't see a link or reference in your response.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Debra,
I'm so sorry for still being stuck here. I went to the link and followed the
examples, but I'm still kind of confused as to how to apply this to my
specific task. If it's not too much to ask, I would like to restate what i
need to do with a short example of how the data must flow. In the example
below I only mention 3 procedures/items but there will be more than 30, and
need room to add more in the future, if possible. Here goes:

Let's say the 1st column needs a drop-down list with 3 Procedures: Back
Adjustment, Massage, Nutrition Book
The 2nd column needs a drop-down list for the Pricing Types: Regular,
Medicare, Cash
The 3rd column will then automatically fill in the correct pricing according
to the "Procedure and Pricing Type"
(This is because the price is different depending on which of the 3 is
chosen: Regular, Medicare, Cash).

For example purposes, let's say these are the prices:
Regular Medicare Cash
Back Adjustment $80 $40 $60
Massage $60 $20 $40
Nutrition Book $30 $15 $20

The 4th column will be Quantity, and the 5th Total Price (These are just
basic calculations: Price x QTY=TOTAL) This ofcourse is not the hard part for
me, LOL.
--
In God''s Harmony


"Debra Dalgleish" wrote:

There are instructions for dependent data validation he

http://www.contextures.com/xlDataVal02.html

Roddy wrote:
Take a look where? I don't see a link or reference in your response.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Let's say the 1st column needs a drop-down list with 3 Procedures:
Back Adjustment, Massage, Nutrition Book
The 2nd column needs a drop-down list for the Pricing Types: Regular,
Medicare, Cash
The 3rd column will then automatically fill in the correct pricing
according to the "Procedure and Pricing Type"
(This is because the price is different depending on which of the 3 is
chosen: Regular, Medicare, Cash).

For example purposes, let's say these are the prices:
Regular Medicare Cash
Back Adjustment $80 $40 $60
Massage $60 $20 $40
Nutrition Book $30 $15 $20

The 4th column will be Quantity, and the 5th Total Price (These are
just basic calculations: Price x QTY=TOTAL) This ofcourse is not the
hard part for me, LOL.

One way is to start by putting the above price table in Sheet2!A:D.

To fill in the third column of Sheet1, put this in Sheet1!C2 and copy down:
=VLOOKUP(A2,Sheet2!A:D,
IF(B2="Regular",2,IF(B2="Medicare",3,IF(B2="Cash", 4,5))),
FALSE)
The idea of the IF() is to select a column of Sheet2 to use based on what's
in Sheet1!B2.

Debra bailed me out...!

I had her site copied and for-got-about the CTRL-V.

Sorry.

Regards,
Howard

"Roddy" wrote in message
...
I am helping set up a spreadsheet for a physical therapy center. One of the
Excel 2002 worksheets is for their invoices. There are about 33 current
procedures/products, but 3 different prices depending on the means the
procedures/products are paid by. On SHEET 1 is the layout of the invoice
itself with all calculations, and on SHEET 2 is a list of the 33
procedures/products currently offered. The list (range of cells) has been
named and incorporated into a drop-down list on SHEET 1. Now here is the
question:

How can I set this up so that when I select one of the 33
procedures/products from the drop-down list in a cell, it will allow me in
the next column to select (from another drop-down list) 1 of the 3 means
(Cash, Medicare, Insurance Provider, etc) by wich to charge the client
by.
This is because the price is different depending on which of the 3 is
chosen.
Is this possible? If so, please, the simplest way to do this.

I have further questions I would like to post based on this same project,
which include an easy and efficient way to incorporate these Excel Sheets
into a database, but still keeping all the formatting, calculations,
cells,
and the actual look of the way it is designed graphically.
--
In God''s Harmony