Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
HALLELUJAH!!! Thanks MyVeryOwnSelf, such a simple solution. I'm not too
familiar with the VLOOKUP formulas, this has been so helpful. And to L. Howard and Debra, I want to sincerely thank both of you as well, especially for all 3 of you responding so quickly. I prayed and said, "Lord, please have someone send me a quick and simple solution today, so I can take a rest from this!" May He bless you all dearly. I don't know if any of you are still in the mood to help me out on 2 more issues with this project, if you are, here goes. 1) When trying to hide the Alert Errors in certain cells (some have formulas/calculations, totals,etc.), I found in several posts to just go to Conditional Formatting†’Formula Is | =ISERROR(cell reference)†’Format†’then choose white or a color matching the cell color for the text. When a cell has no entry and the Alert Error appears (such as: #DIV/0! or #NAME?), the Conditional Formatting with the color is not a problem, but when there is a total or value in that cell, then it can't be seen because of the Conditional Formatting. Is there a SIMPLE way to just have select cells with the Alert Error to be blank (while still keeping the cell color if it has one) when there is an Alert Error, and others I choose to show "$0.00"? 2) Once the above has been accomplished, is there an easy and efficient way to incorporate these Excel Sheets into a database, while still keeping all the formatting, calculations, cell colors, column placements, text blocks/titles, and the actual look of the way it is designed graphically? I know this is a lot, and for me it is a big task, but I know there must be a way. -- In God''s Harmony "MyVeryOwnSelf" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
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. Here's a different formula for Sheet1!C2 I like better: =OFFSET(Sheet2!$A$1, MATCH(A2,Sheet2!$A:$A,0)-1, MATCH(B2,Sheet2!$1:$1,0)-1) It's more general. The payment types on Sheet2 can change without having to change the formulas on Sheet1. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List and Validation for 3 different amounts
I went to Sheet 2 and changed the prices using both of your formulas, but
both seem to work fine giving the same results. Not sure what the new formula does different. PS If anyone can answer my 2 new questions I'd appreciate it, if not, I will try posting it as a new question. Thanks! -- In God''s Harmony "MyVeryOwnSelf" wrote: 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. Here's a different formula for Sheet1!C2 I like better: =OFFSET(Sheet2!$A$1, MATCH(A2,Sheet2!$A:$A,0)-1, MATCH(B2,Sheet2!$1:$1,0)-1) It's more general. The payment types on Sheet2 can change without having to change the formulas on Sheet1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down List Validation | Excel Discussion (Misc queries) | |||
Drop Down List + Validation | Excel Discussion (Misc queries) | |||
Drop down list in Data Validation | Excel Discussion (Misc queries) | |||
validation list with drop down list of options?? | Excel Worksheet Functions | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel |