+ Reply to Thread
Results 1 to 4 of 4

Large number of if/then-type queries with separate cell references with corresponding data

  1. #1
    Registered User
    Join Date
    11-23-2015
    Location
    Washington, DC
    MS-Off Ver
    Mac 2016
    Posts
    2

    Large number of if/then-type queries with separate cell references with corresponding data

    For a 40-year daily income/expense facility development amortization (not a traditional loan amortization), I’m trying to create a form workbook that automates each development’s amortization by matching dates in the amortization worksheet to an income/expense summary worksheet.

    For example, I would like to have the amortization worksheet test each of its dates to see if a match exists with the dates in the summary income/expense worksheet. For each date where a match exists, the amortization worksheet would then reference the corresponding income/expense amount set forth on the summary sheet (formula replicated per income/expense type, not looking for the formula to figure out what’s expense and what’s income).

    The following is an example of the summary income/expense event worksheet:

    Column A Column B Column C Column D
    Date Income Expense A Expense B
    1/6/2016 $55,000 $30,000
    1/7/2016 $21,000
    1/8/2016 $7,500 $5,000
    1/9/2016
    1/10/2016

    The following is an example of how the amortization worksheet is set up with the dates running for 40 years in their own row with income and several other expenses that occur.

    Column A Column L Column M Column N
    Date Income Expense A Expense B
    1/1/2016
    1/2/2016
    1/3/2016
    1/4/2016
    1/5/2016
    1/6/2016 [Ref B1] [Ref C1]
    1/7/2016 [Ref B2]
    1/8/2016 [Ref B3] [Ref D3]
    1/9/2016
    1/10/2016
    etc……


    If I had no more than 7 instances to test, I would assume to use an “if/then” formula handle this. But with 480 possible income/expense instances, I’m stymied.

    Is there, and what is the best formulaic method for managing something like this? I’ve looked within the forum and see the match/index suggestions, but am not sure if that’s what I should be looking at, or which one.

    Thanks very much in advance for any suggestions.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Large number of if/then-type queries with separate cell references with corresponding

    To many words and no sample worksheet to work with.

    Could you upload a sample?
    Quang PT

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Large number of if/then-type queries with separate cell references with corresponding

    Hi
    Try this formula
    =IFERROR(VLOOKUP($E2,$A$2:$C$31,COLUMN(B$1),FALSE),"")
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where $A$2:$C$31 is the summary income/expense event worksheet
    and copy down and right.
    Regards

  4. #4
    Registered User
    Join Date
    11-23-2015
    Location
    Washington, DC
    MS-Off Ver
    Mac 2016
    Posts
    2

    Re: Large number of if/then-type queries with separate cell references with corresponding

    That did it, thanks so much.

+ 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. Replies: 1
    Last Post: 04-20-2015, 04:36 AM
  2. Absolute Cell References for large amount of cells??
    By Bmacnab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2014, 08:06 AM
  3. [SOLVED] multiple conditions to count large amount of data by Type, month & activity type
    By norfolk_lass in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-03-2014, 12:54 PM
  4. [SOLVED] Data Validation: Unique combination of number and type from another cell
    By jonmadjon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2013, 04:13 PM
  5. Exporting large number of columns along with 1 fixed column into separate text files
    By akshaynr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-13-2010, 11:08 AM
  6. Type Mismatch Error on Large Data Sets
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-04-2010, 04:40 PM
  7. Need to update cell references to separate workbook
    By Irish Rob in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2006, 06:09 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