+ Reply to Thread
Results 1 to 4 of 4

Dropdown list from multiple sheets

  1. #1
    Registered User
    Join Date
    02-24-2022
    Location
    Netherlands
    MS-Off Ver
    2108
    Posts
    2

    Question Dropdown list from multiple sheets

    Hi there!

    I'm kind of a rookie when it comes to Excel, and I am stuck with a problem which I hope somebody can advice/help me on/with. I've attached 2 screenshots of the Excel sheet, so you know what I'm talking about.

    I have an Excel sheet in which I gather quarterly financial data, for 12 companies. It's 4 data points per quarter per company. Every company has a seperate sheet, and there is one summary sheet. In this summary sheet (Attachement called 'Capture 1') I would like to have an overview of all the companies and the 4 data points, filtered on a quarter selected from a dropdown list. So for example, if the user wanted to see the data for all the companies in Quarter 2 of 2022, they could select that in the dropdown list, and it would return all the data for that specific quarter. The input for this are the individual sheets per company (an example is Attachement called 'Capture 2').

    Can anyone tell me if this is at all possible and if yes, how?

    There is another thing that I am wondering, but this is more of an extra. Ideally, the dropdown list would also include, for example, FY 2021 (Full-Year 2021), which is all of the quarterly data for 1 year combined.


    Thanks in advance for the massive help!
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,309

    Re: Dropdown list from multiple sheets

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    02-24-2022
    Location
    Netherlands
    MS-Off Ver
    2108
    Posts
    2

    Re: Dropdown list from multiple sheets

    Hereby the Example sheet. Can anybody let me know how I can do this, and if it is at all possible to also have Full Year numbers (so the sum of all quarters in a certain year). Thanks!

    Raphael
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: Dropdown list from multiple sheets

    Try

    in C7

    =IF(LEFT($B$4,2)="FY",SUMPRODUCT(OFFSET(INDIRECT("'" & B6&"'!$B$6"),MATCH(C$5,INDIRECT("'" &B6&"'!A7:A10"),0),MATCH(--RIGHT($B4,4),INDIRECT("'" &B6 &"'!C1:P1"),0),,4)),IFERROR(INDEX(INDIRECT("'"&$B6&"'!C7:P10"),MATCH(C$5,INDIRECT("'"&$B6&"'!A7:A10"),0),MATCH(--RIGHT($B$4,4),INDIRECT("'"&$B6&"'!C1:M1"),0)+MATCH(LEFT($B$4,2),INDIRECT("'"&$B6&"'!C5:P5"),0)-1),""))

    copy across and down

    It would be better to have all the data on one sheet with a column added (column A) with the Company name

    See sheet "Example" / "Summary2": removes need for INDIRECT which can adversely affect performance
    Attached Files Attached Files
    Last edited by JohnTopley; 02-25-2022 at 09:05 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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: 5
    Last Post: 11-15-2018, 11:02 AM
  2. Unhide/hide multiple sheets depending on dropdown list
    By ja650 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2018, 02:04 PM
  3. Already selected values from DropDown list dynamically updating on multiple sheets
    By nyuffancs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-26-2018, 04:38 PM
  4. [SOLVED] Send Data from Dropdown List to Multiple Sheets in first empty cell in a specified column
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2015, 06:17 AM
  5. Replies: 0
    Last Post: 08-08-2014, 12:12 PM
  6. Dropdown list effects multiple dropdown list
    By pvaldez in forum Excel General
    Replies: 1
    Last Post: 05-07-2010, 02:12 PM
  7. dropdown list, multiple sheets, automatic fill
    By roybean in forum Excel General
    Replies: 3
    Last Post: 12-09-2009, 05:39 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