+ Reply to Thread
Results 1 to 6 of 6

Inserting data from a table to another sheet, multiple entries via drop down menu

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    Fredrikstad, Norway
    MS-Off Ver
    Excel 365
    Posts
    9

    Inserting data from a table to another sheet, multiple entries via drop down menu

    Hi,

    I am making a calculation form for my work, and I'm stuck at a problem.

    I have two sheets, one with the 'database', and one with the actual calculation.
    The table in the database has one main type, then multiple entries under that type.

    The table on the calculation sheet has the same column headings, except for the type.
    What I want, is when I select type via dropdown menu on the calculation sheet, it inserts the whole table under the given type from the databasesheet.
    I could do it via an IF-string, but that would me with lots and lots of code, and any change I would do in the database would give me a days work in updating the code on the calculation form.

    So, I want the code to be dynamic, so I can add more types as I go along, and the dropdown menu updating to new entries (types)

    How can I do this? I've included an example to show you what I mean:
    example_excelforum.xlsx

    Appreciate any help

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Inserting data from a table to another sheet, multiple entries via drop down menu

    The attached file does this for you. I've re-arranged your Database sheet, as you can see, by removing the blanks and putting a type for each entry, and the data does not need to be in any order, so I have sorted by name to show this. The named range Type is now a dynamic range, so it will adjust itself to the number of entries you have - there should be no blanks.

    I've used two columns, F and G, with formulae in them copied down to row 20. This one in F2:

    =IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

    sets up a unique sequence for each entry by type - the hyphens are just there to indicate how far you have copied the formula down. This array formula in G2:

    =IFERROR(INDEX(Type,MATCH(0,COUNTIF($G$1:G1,Type),0)),"")

    extracts the unique entries from column A, and there is a dynamic named range set up on this column, called Uniques.

    In the Calculation sheet, the data validation cell now gets its data from the named range Uniques, so it only shows the valid entries. I've introduced a new column A with this MATCH formula in A6:

    =IFERROR(MATCH($B$2&"_"&ROWS(A$1:A1),Database!F:F,0),"-")

    which finds the row where the first entry for the Type choice made in B2 can be found. This formula in B6:

    =IF(OR(A6="",A6="-"),"",INDEX(Database!B:B,$A6))

    extracts the corresponding data from column B of the Database sheet, and this formula is also copied into C6 and D6 to get data from those columns of the Database sheet. This row of formulae has then been copied down to row 14 , as the hyphens in column A indicate. You may need to copy these formulae, and the ones in the Database sheet, further if you have more data in your real file.

    So, just add data to the Database sheet, and then use the drop-down in B2 of the Calculation sheet to see your filtered data.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-14-2012
    Location
    Fredrikstad, Norway
    MS-Off Ver
    Excel 365
    Posts
    9

    Re: Inserting data from a table to another sheet, multiple entries via drop down menu

    Thank you very much, this might just do the trick!
    Some programming ahead though, so I won't set it to solved just yet.

  4. #4
    Registered User
    Join Date
    06-14-2012
    Location
    Fredrikstad, Norway
    MS-Off Ver
    Excel 365
    Posts
    9

    Re: Inserting data from a table to another sheet, multiple entries via drop down menu

    I've tried to replicate what you did on my excel-sheet, but still can't get the 'uniques' field to work.

    Could you take a look at this file to see what's wrong?

    example_excelforum_new.xlsx

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Inserting data from a table to another sheet, multiple entries via drop down menu

    That formula is an array formula, which means that you have to commit it using the key combination of CTRL-SHIFT-ENTER (CSE) instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula subsequently then you need to use CSE again.

    So, all you have to do is to select cell X2, then click in the formula bar as if to edit it (or press F2), then hold down <CTRL> and <SHIFT> and press the <Enter> key at the same time, and the word Hello should appear. Then you can select that cell again and copy it down in the normal manner.

    Don't forget to copy the formulae beyond the bottom of your data, so they will cope with any new data added.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    06-14-2012
    Location
    Fredrikstad, Norway
    MS-Off Ver
    Excel 365
    Posts
    9

    Re: Inserting data from a table to another sheet, multiple entries via drop down menu

    That did the trick! Thanks a million, now I have a spreadsheet that makes my colleagues gasp in awe

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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