+ Reply to Thread
Results 1 to 12 of 12

Help please with drop down menu linked to other data on other sheets?

  1. #1
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Help please with drop down menu linked to other data on other sheets?

    Hi there,

    I would be grateful for help in how to create a drop down menu, and once selected the data from the other sheets show these details on the menu.
    I have attached a example of what I want.

    So on the menu tab, I would like a drop down menu in cell B3 and when they select one of the names of the other tabs, details from these appear on the menu sheet.

    E.g Bank account name / number appear on the menu as from the other sheets?

    Your help would be much appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    This is fairly easy (using Data Validation and INDIRECT(), but I would question the need for a sheet for each bank? Have you considered using 1 sheet for all banks, with a table containing banks and Categories? That way you could use a standard VLOOKUP() or INDEX/MATCH() function.

    For the Dropdown...
    Click the cell
    click Data tab/Data Tools/Data Validation/Allow = List/Source ='Bank All'!$B$1:$D$1

    A
    B
    C
    D
    1
    Bank Account Name: barclays Lloyds HSBC
    2
    Bank Account Number:
    2001
    2002
    2003
    3
    4
    5
    6
    7
    Bank
    8
    9
    car
    2000
    50
    10
    10
    radio
    1000
    75
    20
    11
    12
    Total
    3000
    125
    30
    13
    14
    15
    GL
    16
    17
    car
    2000
    50
    40
    18
    radio
    1000
    75
    50
    19
    sweets
    50
    20
    50
    20
    21
    Total
    3050
    145
    140
    22
    23
    24
    Variance
    -50
    -20
    -110


    Then you could use this to pull in the data...
    =INDEX('Bank All'!$B$1:$D$24,MATCH(Menu!$A13,'Bank All'!$A$1:$A$24,0),MATCH(Menu!$B$3,'Bank All'!$B$1:$D$1,0))

    Note that the headings need to match, and if yoy want the total, thats the name you need to use
    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

  3. #3
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Help please with drop down menu linked to other data on other sheets?

    Hi Ford,

    Thanks for the quick reply.

    Sorry if I am being thick, but I don't quite follow what you are saying in terms of using INDIRECT (which is what I would like to use).

    There are multiple sheets and many more accounts as these contain more details and can have upto 200 rows for the transactions.

    I would be grateful if you can show how a drop down menu is created and linked so data is pulled from various accounts to the menu?

    Thanks Ford,

    Regards

    Nim

  4. #4
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    I would be grateful if you can show how a drop down menu is created
    Thats what I did here...
    For the Dropdown...
    Click the cell
    click Data tab/Data Tools/Data Validation/Allow = List/Source ='Bank All'!$B$1:$D$1
    If you indend to use different sheets for each bank (I still think you could use 1 sheet), you would need to have a list if each sheet (Bank) name, to use in the DropDown.

    In the attached file, I have given you both ways...Menu (2) and Bank All are the combined sheets. The others are what you had.

    Bank on Menu had a trailing spoace, you need to remove that.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Help please with drop down menu linked to other data on other sheets?

    Hi Ford,

    Many thanks for this. This helps a lot. Just to clarify, as I intend to use the second option (menu with accounts on different sheets), is it hard to take it a step further and create a button, so for example if they select HSBC from the drop down menu and want to see full details on HSBC, they click on the button and it goes straight to that worktab.

    This would save time as there are going to be many accounts (over 15).

    Thanks Ford.

    Also, not sure what you mean by I had a trailing spoace which I need to remove?

    Regards

    Nim

  6. #6
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    Also, not sure what you mean by I had a trailing spoace which I need to remove?
    In the File you uploaded, look at cell A20. When you edit it, there is a soace at the end of Bank...we call that a trailing space. "Bank" is not the same as "bank "

    This is kind of a button, its called a Hyperlink, when you click it, it will take you to the specified sheet. Put it wherever you want it to be, just make sure that the bolded part is the same reference as the drop-down
    =HYPERLINK("#"&"'"&B3&"'!A1","Click Here to go to "&B3&" Sheet")
    The A1 reference, is the location of the other sheet that you want the cursor to go to - you can adjust that as needed.

    As an added bonus, you can put this at the top (or wherever) of each sheet to take you back to the Menu sheet...
    =HYPERLINK("#"&"'Menu'!A1","Click Here to go to Menu Sheet")

  7. #7
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Help please with drop down menu linked to other data on other sheets?

    Hi Ford,

    Not done this before. Are you able to show how a button / hyperlink is created, or create one so I can do the added bonus one

    Also, what is the effect of having a trailing space?

    Do I have to change the name from Bank to bank?

    Thanks Ford

    Regards

    Nim

  8. #8
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    Also, what is the effect of having a trailing space?

    Do I have to change the name from Bank to bank?
    Take a look at the yellow highlight range in the attached.
    D20 is the word Bank with a trailing space
    D21 is the word Bank with no trailing space
    Excel considers them to be not the same. A space is a character, just like any other letter or number.
    In the same way, these 2 strngs are not the same...
    "This contains spaces"
    "Thiscontainsspaces"

    By including an extra space at the end of a word, that word then becomes a different word.
    ------------------------------------
    In the attached, I have indicated where the Hyperlink formula is. If you want to see/edit it, either click/hold on it (otherwise it will activate, and take you there), or use the keyboard to navigate there
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Help please with drop down menu linked to other data on other sheets?

    Hi Ford,

    Many thanks again for your help with this. I am going to go through the spreadsheet attached and try to understand how the hyperlink / trailing space works.
    No doubt I will email you again for your help!

    I see you have also added the button 'back to main menu' on bank 1 and 2. Is there an easy way to do this to each work tab, or would it have to be done manually (as there will be many more accounts)?

    Thanks Ford,

    Regards

    Nim

  10. #10
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    Is there an easy way to do this to each work tab, or would it have to be done manually (as there will be many more accounts)?
    Put that in the 1st bank sheet
    scroll to the last sheet
    hold the shift abd click on the last bank tab name
    You have now "Grouped" all those sheets - what you do on 1, will be carried through to them all

    you should still be on the 1st sheet with that formula in it
    F2 on the formula, then press Enter. It should now be in that came cell across off of the sheets
    Click on the Menu sheet to Ungroup (or rt-click a sheet tab and select Uncroup)

    As far as the traling space is concerned, dont make a big deal out of it. It just means that when you typed the word in, you added a space at the end by mistake. I was just pointing out that mistake and suggested that you fix it. It is not some fancy excel function or trick, simply a commom mistake we all make now and then

  11. #11
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Help please with drop down menu linked to other data on other sheets? - COUNT FORMULA

    Hi Ford,

    Thanks again for your help with this and how to easily setup buttons by selecting all tabs.

    I have a query regarding the COUNT formula.

    What I would like to do is a add a column next to another column that has the date specified.
    So the count function will count how many days the transaction has been on there.

    For example, If the date in the column is 4th July, the count formula would show 2 (if the date was 22nd June, the formula would show 14).

    I hope you can help?

    Thanks and Regards

    Nim

  12. #12
    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,917

    Re: Help please with drop down menu linked to other data on other sheets?

    So the count function will count how many days the transaction has been on there.
    meaning from today's date (that will update as today's date changes?)

    If that is what you meant, here is how I would handle that.
    1. in a cell somewhere (doesnt really matter where) enter =TODAY() (for the purposes of this, lets say you put that in J1)
    2. In the "next" column, all you need to do is say...
    =C1-$J$1
    (assuming the date is in C1)

    Now, if you want this to stop counting when C1 <= today's date, then that formula would become...
    =IF(C1>=$J$1,"",C1-$J$1)

    Let me know how you make out with this?

+ 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: 3
    Last Post: 09-17-2013, 10:45 PM
  2. How to use drop down menu to populate data to other sheets
    By andy.k in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-06-2013, 08:59 PM
  3. [SOLVED] Data transfer from two different sheets based on drop down menu using a button
    By rowlandjp in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-06-2013, 12:51 PM
  4. Replies: 2
    Last Post: 12-23-2010, 09:30 PM
  5. Drop Down menu & linked cells
    By darken99 in forum Excel General
    Replies: 4
    Last Post: 05-30-2007, 03:44 PM

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