+ Reply to Thread
Results 1 to 6 of 6

How to get the contents of one cell to determine what sum to do in another

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    How to get the contents of one cell to determine what sum to do in another

    I have attached a sample workbook, if you could take a look please.
    With the help and patience of this forum I created a very successful workbook for a local charity who hire out disability equipment.
    They would now like to have a different workbook for hiring other items and the conditions are nothing like the other book

    There are 3 types of hirers
    C = Casual
    D = Day
    M = Members

    Casual, get charged £3 for anything up to 3 hours
    Day, get charged £5 for the day or over 3 hours (they are all returned that day, no overnight, so the max would be 9 hours)
    M, don't get charged because they pay a subcription

    The attached sheet has the following columns
    'F' start Time. 'G' End time 'H' Hours hired 'I' Hire Cost

    What I would like, if it can be achieved is for the hire cost to be controlled by column 'C'

    If you enter 'C' then column 'I' calculates G-F and if under 3 hours charges £3
    If you enter 'D' then column 'I' calculates G- F and if over 3 hours charges £5
    If you enter 'M' then column 'I' just returns £0.00 because there is no charge.

    The price of £3 and £5 would be picked up from Cells M% & M6 because that's a contigency for the future to allow for any price changes, rather than have to change all the formulas

    Can it be done? or do you have any better ideas, they are very welcome
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to get the contents of one cell to determine what sum to do in another

    Hi,

    One way in I13
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to get the contents of one cell to determine what sum to do in another

    hi there again nje. so there's a possibility column C is not filled? if no, then maybe:
    =IF(C5="C",$M$5,IF(C5="D",$M$6,IF(C5="M",$M$7,"")))

    otherwise, Richard's formula would be more versatile in dealing with scenarios without values in Column C

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: How to get the contents of one cell to determine what sum to do in another

    Richard, thank you, excellent solution and you were both so quick in responding, Benishiryo, again many thanks for your contributions, but Column 'C' will remain blank.
    I couild do with the £0.00 in 'I' not showing if C, F and G and not filled in if possible
    Thanks again

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to get the contents of one cell to determine what sum to do in another

    Quote Originally Posted by nje View Post
    I couild do with the £0.00 in 'I' not showing if C, F and G and not filled in if possible
    Thanks again
    Just preface the original formula with another IF test, i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or maybe use a conditional format in I13 to set the font colour to be white if c13 = ""

  6. #6
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: How to get the contents of one cell to determine what sum to do in another

    Thanks for your perseverance Richard, a true professional

+ 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