+ Reply to Thread
Results 1 to 10 of 10

Drop down list with information in Excel

  1. 02-17-2019, 12:41 PM #1
    Shan54321
    Shan54321 is offline
    Registered User
    Join Date
    05-29-2018
    Location
    UAE
    MS-Off Ver
    2019
    Posts
    75

    Drop down list with information in Excel

    Hi ,

    I have a long list of data with customers payment plan in one tab and the second tab i have information about the customer paid amount .

    I need these data to extract from a master tab by selecting drop down list .Can any one help.

    Drop down list should be plot number and by selecting the plot number i want to see the customer name total amount paid and payment plan .
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-17-2019, 02:05 PM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Drop down list with information in Excel

    Please try at
    C3
    =SUMIFS('Tab-2'!D:D,'Tab-2'!C:C,B3)

    D3
    =SUMPRODUCT(('Tab-3'!$C$4:$C$20=B3)*'Tab-3'!$D$4:$I$20)
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 02-17-2019, 02:47 PM #3
    Shan54321
    Shan54321 is offline
    Registered User
    Join Date
    05-29-2018
    Location
    UAE
    MS-Off Ver
    2019
    Posts
    75

    Re: Drop down list with information in Excel

    Hi ,

    This formula really help me . Now can you help how to know the year of payment plan for individual amount .

    I need the formula in Master tab E3 Column , which need to extract data from Tab-3 .

    So when i select the plot number from drop down list , so i can see which year the customer has payment plan .
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-17-2019, 03:08 PM #4
    Shan54321
    Shan54321 is offline
    Registered User
    Join Date
    05-29-2018
    Location
    UAE
    MS-Off Ver
    2019
    Posts
    75

    Re: Drop down list with information in Excel

    Hi ,

    This formula really help me . Now can you help how to know the year of payment plan for individual amount .

    I need the formula in Master tab E3 Column , which need to extract data from Tab-3 .

    So when i select the plot number from drop down list , so i can see which year the customer has payment plan .
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-18-2019, 12:14 AM #5
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Drop down list with information in Excel

    You need Excel 365 Textjoin to display in 1 cell

    =TEXTJOIN(",",,INDEX(REPT(YEAR('Tab-3'!$D$3:$I$3),MMULT(COLUMN(INDIRECT("c1:c"&ROWS('Tab-3'!$C$4:$C$10),))^0,(B3='Tab-3'!$C$4:$C$10)*('Tab-3'!$D$4:$I$10>0))),))

    or F3 drag to the right

    =IFERROR(AGGREGATE(15,6,YEAR('Tab-3'!$D$3:$I$3)/(MMULT(COLUMN(INDIRECT("c1:c"&ROWS('Tab-3'!$C$4:$C$10),))^0,($B3='Tab-3'!$C$4:$C$10)*('Tab-3'!$D$4:$I$10>0))),COLUMNS($E3:E3)),"")
    E3
    =F3&" "&G3&" "&H3&" "&I3
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-18-2019, 01:23 AM #6
    FDibbins
    FDibbins is offline
    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,933

    Re: Drop down list with information in Excel

    Quote Originally Posted by Bo_Ry View Post
    You need Excel 365 Textjoin to display in 1 cell...
    It appears OP is using 2010, which does not have TEXTJOIN
    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
    Reply With QuoteRegister To Reply
  7. 02-18-2019, 01:42 AM #7
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Drop down list with information in Excel

    Ford, Thanks.
    Just in case he didn't update profile.
    Reply With QuoteRegister To Reply
  8. 02-18-2019, 01:43 AM #8
    FDibbins
    FDibbins is offline
    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,933

    Re: Drop down list with information in Excel

    Bo_Ty, yup, I have seen that before, too
    Reply With QuoteRegister To Reply
  9. 02-18-2019, 03:15 PM #9
    Shan54321
    Shan54321 is offline
    Registered User
    Join Date
    05-29-2018
    Location
    UAE
    MS-Off Ver
    2019
    Posts
    75

    Re: Drop down list with information in Excel

    Thanks a lot . It really helped me . Just want to know how to see the month along with the year ??
    Reply With QuoteRegister To Reply
  10. 02-19-2019, 10:12 AM #10
    Shan54321
    Shan54321 is offline
    Registered User
    Join Date
    05-29-2018
    Location
    UAE
    MS-Off Ver
    2019
    Posts
    75

    Re: Drop down list with information in Excel

    Hi dear ,

    When i use this formula it shows error ?? Can u help me again.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Drop down list to fill information.
    By brainkill3r in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-17-2019, 09:20 PM
  2. [SOLVED] Drop down list must be entered before another cell can have information
    By deraj in forum Excel General
    Replies: 3
    Last Post: 12-10-2015, 12:17 AM
  3. Information into certain tabs dependent on drop down list
    By Cuffy70 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-21-2015, 06:55 AM
  4. Information from a drop down list selection
    By Evander in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 03:43 PM
  5. How to get corresponding information to show when choosing from drop down list.
    By anna.ring425 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2012, 11:30 AM
  6. Drop down list - rich information experience
    By kccboy2004 in forum Excel General
    Replies: 3
    Last Post: 05-28-2008, 10:16 AM
  7. many bits of information:Drop Down List
    By travelersway in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-17-2005, 02:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1