+ Reply to Thread
Results 1 to 8 of 8

If cell contains text then return value in another cell.

  1. #1
    Registered User
    Join Date
    06-30-2013
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    24

    If cell contains text then return value in another cell.

    Hey, I'm in dire need of your help.
    I use Excel to manage my bank and to make life easier I need the following doing (if possible)

    See attached.. I have a list of my bills along with it's Due Date and if it's been paid or not (The "Paid" column has drop-down menu so I can select "Yes" or "No")

    Example:

    If E10 is displaying "Yes" then D10 will display £0.00
    If E10 is displaying "No" then D10 will display whatever number I want it too

    &

    When I enter a number greater than £0, it changes the "Paid" on E10 to "No"

    This way I can enter my monthly bills, and once I've paid it, I can select "Yes" and it'll automatically change the "Due Now" to £0, resulting in the total being correct for the bills I've got left to pay, and then when I enter a new amount for my bill next month, it'll change D10 back to "No" until I select "Yes" again.

    Then also, if possible, once I enter a number into any of the "Due Now" columns, then it changes the "Paid" to "No"
    Is there any way to do this?

    Thanks in advance for your help, if you need any more information then let me know.
    Attached Images Attached Images
    Last edited by Lifeless; 06-30-2013 at 08:54 PM.

  2. #2
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: If cell contains text then return value in another cell.

    Can you upload your spreadsheet - think i know a slightly better way of doing it for you.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If cell contains text then return value in another cell.

    Hi and welcome to the forum

    I have a question on the 2nd part of this...
    If E10 is displaying "Yes" then D10 will display £0.00
    If E10 is displaying "No" then D10 will display whatever number I want it too
    Do you mean that if E10 says YES, then you want D10 to show 0, but if it shows NO, then you want to enter your own value?

    If thats the cased, then you cannot do this with a regular formula. A cell can either contain a formula OR a value/text/entry - it cannot contain both

    If thats not what you meant, then maybe...

    D10 =if(E10="yes",0,whatever-amount-you-want-here)

    Likewise, the cell with the drop-down can either contain the contents OF the D/D or it can contain a formula - not both
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-30-2013
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    24

    Re: If cell contains text then return value in another cell.

    Quote Originally Posted by Crawfinator1 View Post
    Can you upload your spreadsheet - think i know a slightly better way of doing it for you.
    Uploaded an edited version for you guys.
    Thanks for your quick replies!

    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    I have a question on the 2nd part of this...


    Do you mean that if E10 says YES, then you want D10 to show 0, but if it shows NO, then you want to enter your own value?

    If thats the cased, then you cannot do this with a regular formula. A cell can either contain a formula OR a value/text/entry - it cannot contain both

    If thats not what you meant, then maybe...

    D10 =if(E10="yes",0,whatever-amount-you-want-here)

    Likewise, the cell with the drop-down can either contain the contents OF the D/D or it can contain a formula - not both
    Yes you are correct in your first assumption... You say it's not possible with a regular formula, does that mean that it IS possible... Just not with a regular formula?
    If you see my attached spreadsheet, on the "Bills & Things" page you'll perhaps see what I'm trying to do in more detail.

    But yes, if E10 says YES, then I'd like D10 to show 0 as it would have been paid for, therefore I wouldn't want it calculated in the total, however the cell D10 needs to be
    changed on a monthly basis to reflect the current months bills. Then when the month starts again, I can input a number into D10 and it'll automatically change the E10 to display "No" again as I wouldn't have paid it yet.

    I'm just using D10 as 1 example.. The actual cells of my bills table is:

    D10:D17 - That has the cost of my bills in there

    E10:E17 - That has the drop-down menu of "Yes" or "No"
    Attached Files Attached Files
    Last edited by Lifeless; 07-01-2013 at 08:41 AM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If cell contains text then return value in another cell.

    It can probably be done using VBA, but VBA is not my strong side

  6. #6
    Registered User
    Join Date
    06-30-2013
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    24

    Re: If cell contains text then return value in another cell.

    Quote Originally Posted by FDibbins View Post
    It can probably be done using VBA, but VBA is not my strong side
    Ah damn. Thanks anyway buddy!
    Is there anyone you can possibly direct to this thread who can help me out?

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: If cell contains text then return value in another cell.

    Why don't you just switch the order around?

    For the D column cells - enter what is due or a 0, and then in column E simply have =IF(D>0,"Yes","No")

    Why do you even need the Paid column anyway? If it is for filtering purposes you could simply filter for values in column D that are >0.

    That spreadsheet has so much potential, you could list your bills on the "Bills & Things" sheet and then when the bills are paid and come through the Statement sheet you could have an auto list in the description to allocate the payment against any relevant bills that are unpaid - automatically filling out the statement and bills sheets to show it has been paid.....

  8. #8
    Registered User
    Join Date
    06-30-2013
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    24

    Re: If cell contains text then return value in another cell.

    Wow I feel really stupid. No idea why I didn't think of that myself with adding in =IF(D10>0,"Yes","No") - I guess that works just as well as what I was trying to do. I also added conditional formatting to color code the paid columns so it's easy for me to take a glance at which bills are paid and not paid... Just because I hate staring at a spreadsheet filled with numbers all the time, it's nice to be able to take a glance and see whats left.

    Then about your second suggestion...
    Do you mean I can use some sort of conditional formatting to get it to automatically fill out my bill section for me when I enter it from my statements sheet? So if I for example enter "Rent" under the description of my statement, then it can pick up on that and add the cost of that onto my bills page? If so how would I go about doing that?

    Also, do you have any suggestions as to how I can improve the usability of my statements page? Ideally I'd like to be able to input my new bank statements from the top of the page, as opposed to the bottom where I'm currently inputting them, but of course if I input them from the top, I'll need to keep adding in a row manually, which I don't want to have to do... and it would also be nice if it auto deletes my bank statements which are older than 2 months. Is something like that possible?

    Thanks again for your help guys!

+ 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