+ Reply to Thread
Results 1 to 7 of 7

Data VAlidation With If statement

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Data VAlidation With If statement

    Consider i have an amount e.g. 100,000 in A1. Now this amount can be divided equally on 24 months or 36 months i.e. 100,000/24 or 100,000/36.

    I want to create formula that if i select 2 years in validation column( "A2" ) it Automatically divide the A1 value by relevant no of months and map the result monthly from January to December in appropriate columns. I have attached a sample file of what i want.

    Please have a look.
    Attached Files Attached Files

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Data VAlidation With If statement

    Hi!

    See if this works for you.

    Deep
    Attached Files Attached Files
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    07-20-2013
    Location
    Sechelt, BC, Canada
    MS-Off Ver
    Excel 2011 (Mac) and 2007/2010/2013 (Win 7)
    Posts
    6

    Re: Data VAlidation With If statement

    I would suggest having input boxes for the year, but having the number format for it (ctrl+1) being Custom: 0 "years". That way you just enter a whole number like "3" and it will say "3 years". Then you can make calculations with it—e.g. derive the number of months as being "x years" * 12. Then I'd have a start date that you want. Then make a bunch of if statements based on the number of columns across the cell is at to determine if it's ≤ the number of months… if so, put it down, otherwise make it "". A similar function would determine the payment.

    I've attached a file to this which shows what I'm getting at.

    Book1-editedbyScott.xlsx

    -Scott

  4. #4
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Data VAlidation With If statement

    see this attachment

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Data VAlidation With If statement

    Ghozi Alkatiris formula is simple and straight.

    You should go for that.

    Deep

  6. #6
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Data VAlidation With If statement

    Quote Originally Posted by Ghozi Alkatiri View Post
    see this attachment
    Thanks for the effort. But in your sheet installments doesn't adds up to total.

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Data VAlidation With If statement

    Just change his formula to -

    =IF($C6>COLUMNS($D:D)-1,$A6/$C6,"") in cell D6

    Deep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data validation and If statement
    By tjohans in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2013, 05:20 PM
  2. DATA Validation and IF statement
    By NCAA in forum Excel General
    Replies: 3
    Last Post: 03-29-2012, 04:01 AM
  3. Data validation in if statement
    By mahoo in forum Excel General
    Replies: 3
    Last Post: 02-15-2011, 04:06 PM
  4. Data validation IF statement
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 11-12-2009, 11:27 AM
  5. [SOLVED] using the if statement with data validation
    By GreenMonster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2005, 04:06 PM

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