lookup and autonumber
 

I will be grateful if I can receive help regarding this problem:
I am trying to build an invoice workbook for my clinic. I have 2
problems:
1. I have almost 25 different procedures and 4 different room
categories. The professional charges for the procedures vary according
to the room category e.g for procedure x, the charge will be 5 for
general room, 8 for semispl room, 13 for spl room etc. with no fixed
ratio of increase.In the invoice form, as soon as i select the room
type and procedure, I need to get the price automatically. I have made
a table assigning the prices. I have tried the vlookup, offset but to
no avail. The table is on a different worksheet to avoid the clutter.
The table is unsorted.
2. I wish to assign the invoice number automatically serially to the
bills generated.
Any help will be greatly appreciated.
thanks,
rajesh


--
rajeshparikh64
------------------------------------------------------------------------
rajeshparikh64's Profile: http://www.excelforum.com/member.php...o&userid=32830
View this thread: http://www.excelforum.com/showthread...hreadid=526314

lookup and autonumber
 

Set your table as follows:
In sheet 2 across the range A1:E26 where on row 1 cell B1 thru E1 holds
room number headings and A2 thru A26 holds the decription.

Now enter the rates in the range B2:E26 so that you enter all 25 * 4 = 100
rates in these cells

Now whereever you want the rate use this formu

=INDEX(Sheet2!$B$2:$E$26,MATCH(F1,Sheet2!$A$2:$A$2 6,0),MATCH(G1,Sheet2!$B$1:$E$1,0))

where F1 holds the procedure and G1 holds the room number in the above
formula

As far as automatic invoice numbering is concerned do a quick lookup in
google and you will find a small VB script to do that





"rajeshparikh64"
<rajeshparikh64.259gqm_1143349801.8909@excelforu m-nospam.com wrote in
message news:rajeshparikh64.259gqm_1143349801.8909@excelfo rum-nospam.com...

I will be grateful if I can receive help regarding this problem:
I am trying to build an invoice workbook for my clinic. I have 2
problems:
1. I have almost 25 different procedures and 4 different room
categories. The professional charges for the procedures vary according
to the room category e.g for procedure x, the charge will be 5 for
general room, 8 for semispl room, 13 for spl room etc. with no fixed
ratio of increase.In the invoice form, as soon as i select the room
type and procedure, I need to get the price automatically. I have made
a table assigning the prices. I have tried the vlookup, offset but to
no avail. The table is on a different worksheet to avoid the clutter.
The table is unsorted.
2. I wish to assign the invoice number automatically serially to the
bills generated.
Any help will be greatly appreciated.
thanks,
rajesh


--
rajeshparikh64
------------------------------------------------------------------------
rajeshparikh64's Profile:
http://www.excelforum.com/member.php...o&userid=32830
View this thread: http://www.excelforum.com/showthread...hreadid=526314

lookup and autonumber
 

thanks for the help. really needed it with the new financial year
starting an everything


--
rajeshparikh64
------------------------------------------------------------------------
rajeshparikh64's Profile: http://www.excelforum.com/member.php...o&userid=32830
View this thread: http://www.excelforum.com/showthread...hreadid=526314