+ Reply to Thread
Results 1 to 3 of 3

lookup and autonumber

  1. #1
    Registered User
    Join Date
    03-26-2006
    Posts
    5

    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

  2. #2
    N Harkawat
    Guest

    Re: 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$26,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"
    <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  3. #3
    Registered User
    Join Date
    03-26-2006
    Posts
    5

    Thumbs up Thank you

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1