+ Reply to Thread
Results 1 to 6 of 6

Help returning values from columns based on user selected criteria

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Help returning values from columns based on user selected criteria

    This is the next steps of some much appreciated help I got from JeteMC and Czeslaw in a previous post. I'm closer than ever to getting this figured out (I think) but I'll try explain my challenges as simply as possible.

    This is a custom project building / invoicing workbook with two tabs.

    On the "Buildout" tab, I want to build a job using specific line items in a table with information relevant to me. (You'll see that part is built already.)

    On the "Invoice1" tab, I want the ability to create an invoice based on which bill line items were billed on. This part is progressing nicely, but I'm stuck.

    If you look at the attached file, you can see that the info that populates the table on the "Buildout" tab is pulled into the "Invoice1" tab using an Array formula... Like this...

    =IFERROR(INDEX(Table1[Date],SMALL(IFERROR(ROW(A$1:A$100)/(Table1[Inv]=D$5),FALSE),ROW(A1))),"")

    Here are some problems I need help solving...

    1. When I use the pull-down menu in the Invoice1 Tab at Cell D5, I expect it to pull in all line items from the selected criteria, but it only pulls in one of many lines that match. You'll see this result when you change it from 1 to 2 and back again. What did I miss? [SOLVED] - uploaded corrected file - thank you mike7952

    2. The Table in the "Buildout" tab has columns that allow user to enter billing increments. (For example, if a line item was $1,000 but was going to be broken into two billing invoices, user could enter $500 in Bill1 and $500 in Bill2 to break them up and keep them organized by date of billing. I want to make it so when the line items populate in the "Invoice1" tab after selecting the desired criteria in D5, the amount billed for that criteria shows up.) Like this... User selects "1" in "Invoice1" Cell D5. All the line items from the table in "Buildout" that correspond to the value selected in D5 list on the Invoice1 tab populate. Under the "Amount Due" column, the formula retrieves the dollar amount that matches the Bill1, Bill2, etc...)

    In the attached example, you can see that my current capabilities are unable to 1. get ALL the line items related to the D5 selection to list, and 2. "Amount Due" is currently just pulling from the column "ActualVE" where I need it to find the dollar amount under Bill1 or Bill2 etc based on the D5 selection.

    Simple but complex? I hope I did the problem some justice trying to explain it. The attached sheet should bring some clarity.

    Thanks in advance...

    Pete
    Last edited by petelozzi; 12-04-2016 at 06:27 PM. Reason: Made correction as directed by moderator.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Seemingly simple project that keeps unraveling to reveal more complexity...

    Why is the formula In row 9 different then what is in row 8? I dragged row 8 down a few rows and when changing the drop down from 1 to 2 all data showed up
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Seemingly simple project that keeps unraveling to reveal more complexity...

    Thank you mike7954! I'm embarrassed that I missed that. Part one solved!

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Seemingly simple project that keeps unraveling to reveal more complexity...

    I'm not sure that your second request can be done with a formula, probably require a Maco. Maybe someone else will have a formula solution.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Seemingly simple project that keeps unraveling to reveal more complexity...

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Seemingly simple project that keeps unraveling to reveal more complexity...

    Done. Thank you and apologies for non-compliance.

+ 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. Newbie at Macros - tips for seemingly simple macro?
    By Drexl27 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-24-2015, 01:41 PM
  2. Seemingly simple retrieval of Pivot Table data
    By DJCurtley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2013, 09:05 PM
  3. Issue with seemingly simple SUM formula
    By jwesterfield in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-25-2013, 09:28 AM
  4. Nested If/And to reveal project status
    By wels in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2010, 12:20 PM
  5. Using MS Excel 2K7 to manage simple project!
    By nichya88 in forum Excel General
    Replies: 6
    Last Post: 10-25-2009, 01:28 PM
  6. Seemingly Simple But Odd Problem Question
    By xfreez99 in forum Excel General
    Replies: 2
    Last Post: 07-28-2008, 09:23 AM

Tags for this Thread

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