+ Reply to Thread
Results 1 to 4 of 4

Better Way then IF statements ?

  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    25

    Better Way then IF statements ?

    Hi,

    I am trying to create a tracker for cash variances among my staff. I have a worksheet for each day of the month and a Variance worksheet that will hold all the compiled information. Attached is an short version of my workbook. The yellow cells are the cells that will contain the information I need.

    In Cells G26:G28 will be the names of the cashiers. In cells H26:h28 will the a dollar amount and in cells I26:I28 will be how many times it occurred. My If statment looks like the following : =IF('1'!G26="Billy",'1'!H26*'1'!I26,0). How ever if Billys name is put into G27, then I'd like H27 to multiply by I27. Since I have at least 30 worksheets and approximately 15 staff members writing three different If statments for each will become extremely exhausting.

    Is there a better way to do this ?

    Thanks for your Help

    Example to post.xls

  2. #2
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184

    Re: Better Way then IF statements ?

    The simplest way would be to sum up regardless of the name; in the Variances sheet.
    e.g. in the cashier column put ='1'!G26 to get the name of that cashier and then ='1'!H26*'1'!I26 in the 1$ column (then you wouldn't ned IF statements, and you could always autosorr the list alphabetically afterwards)
    _______________________________________________
    Remember:

    Read the rules!

    Use code tags! Place [CODE] before the first line of code and [/CODE] after the last line of code.

    Mark your post [SOLVED] if it has been answered satisfactorily.
    To do this, click EDIT in your original post, click GO ADVANCED and set the PREFIX. It helps everybody!

    If you are approve (or disapprove) of any members response to your problem, please click the star icon underneath their post and comment. It matters.

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Better Way then IF statements ?

    hi diggy13, please check VB option to solve your issue. If I understood you right, the Variancees sheet row 2 holds Sheet names from 1 to 31 plus Cashier name and Total. Press "Collect data". The code would collect cashier names on each of sheets (1 to 31) with names and calculate necessary amounts.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by watersev; 12-14-2011 at 05:35 AM.

  4. #4
    Registered User
    Join Date
    06-30-2010
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Better Way then IF statements ?

    Thanks Waterserv ! This will work great for my setup. I never thought of using a VB option. Greatly appreciated .... thank you for taking the time.

    Cheers,

+ 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