Closed Thread
Results 1 to 7 of 7

Updating cells based on selection from drop down list data validation

  1. #1
    Registered User
    Join Date
    11-25-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Updating cells based on selection from drop down list data validation

    Hi all,

    I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.

    Basically i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab. Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
    Attached Files Attached Files
    Last edited by excelstun; 12-14-2009 at 02:24 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating cells based on selection from drop down list data validation

    Like so...just make sure the categories on the second sheet match the categories on the first exactly....
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-25-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Updating cells based on selection from drop down list data validation

    Hi JBeaucaire,

    Thanks a lot, works like a charm.

    Is there a way to automatically update the sheet name to the month that is selected from the drop down list?

    Thanks again.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating cells based on selection from drop down list data validation

    Not without VBA. I prefer to go the other way and have cell B1 have the following formula in it:

    =MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1, 256)

    This way the cell updates itself based on the sheet name and then the formulas auto-update.

  5. #5
    Registered User
    Join Date
    11-25-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Updating cells based on selection from drop down list data validation

    I see. This is so cool, thanks a lot for your help.

    Cheers

  6. #6
    Registered User
    Join Date
    01-07-2016
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    1

    Re: Updating cells based on selection from drop down list data validation

    HI JBeaucaire,

    Let's say if I have 3 tabs and I have dropdown as the names of the tabs, I need to fetch data from each tab based on the dropdown selected. How would I need to modify the formula which you had on the excel? thanks in advance.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Updating cells based on selection from drop down list data validation

    shaikeelim,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

Closed Thread

Thread Information

Users Browsing this Thread

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

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