+ Reply to Thread
Results 1 to 2 of 2

using excel to generate super- bills

  1. #1
    Registered User
    Join Date
    05-18-2012
    Location
    NY, US
    MS-Off Ver
    Excel 2010
    Posts
    1

    using excel to generate super- bills

    Hi
    I'm using excel to create super-bills (see example 1 below). But it looks somewhat crude and rudimentary. I have an idea to improve it. but I can't figure out how to do the programing/functions. Any help is appreciated.

    Currently I place an x next to services that i render (under the col labeled MARKER), which triggers the fee to be listed under the OFFICE FEE col. (The formula in OFFICE FEE is: =IF(B2="x",E2," "))
    The Office Fees are added on the bottom of the bill.
    What I'd like to do is to search the col labeled as MARKER for "x", and if there are an rows with "x", copy that entire row into a new array of data, thereby eliminating the services that are not utilized from the service bill (see example 2 below). The key problem is that each time could be a number of simultaneous services rendered, necessitating placement of multiple x's in the same col, to trigger the formulas.

    QUESTION: is there a a way to search MARKER, and if MARKER has an "x", then the entire row with an "x" is displayed?

    Example 1: Sample of existing super-bill

    A1 MARKER Code Description Fee OFFICE FEE
    A2 x D3347 Service 1 $766.00 $766.00
    A3 D3348 Service 2 $881.00
    A4 D2955 Service 3 $189.00
    A5 D2940 Service 4 $66.00
    A6 D2954 Service 5 $243.00
    A7 x D9951 Service 6 $106.00 $106.00
    A8 x D7510 Service 7 $83.00 $83.00
    A9 D3910 Service 8 $50.00
    A10 D2920 Service 9 $50.00
    A11 D1930 Service 10 $50.00
    Total $955.00

    Example 2: Future bill
    A1 MARKER Code Description Fee OFFICE FEE
    A2 x D3347 Service 1 $766.00 $766.00
    A3 x D9951 Service 6 $106.00 $106.00
    A4 x D7510 Service 7 $83.00 $83.00
    A5 Total $955.00

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: using excel to generate super- bills

    You could add a column to the left of the Marker column (and later hide it if you like). In that column would be a formula that would return the row number if the Marker cell contained an "x".
    Your Future Bill area would have VLOOKUP()s (with error checks) using the SMALL() function as the lookup values.

    So something like:
    =IFERROR(VLOOKUP(SMALL(A1:A11,1),A1:E1,2,0),"")
    change the 1 in the SMALL() function for subsequent rows

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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