Dropdown Box to Select Data from Another Sheet


A

Annabelle

How might this be done?

I have an accounting workbook with two sheets, Master and
Data Entry. I want to be able to enter data on the Data
Entry sheet for each month of the year (example below).
Each month has the same number of rows and columns. On the
Master sheet, instead of 12 "tables", there is only 1 and
it is empty. Using a dropdown box (where the month/year
should be - as below) so I can view the data for that
specific month. If I select JAN, 03 from the dropdown, the
table will present the data from the Data Entry sheet for
January '03. If I want to see the data for March '03, I
select MAR, )# from the dropdown and March '03 is now
presented.


A B C D E F
JAN, 03
Sales (x$1000) Revenue (x$1000)

# Product/Category Budget Actual Budget
001 Product 1 1,200 1,000 900 700
002 Product 2 2,000 1,800 1,500 1,300
003 Product 3 3,000 2,200 2,100 2,200
004 Product 4 100 60 70 50
005 0

FEB, 03
Sales (x$1000) Revenue (x$1000)

# Product/Category Budget Actual Budget
001 Product 1 1,500 1,200 1,000 900
002 Product 2 3,000 2,800 2,500 2,300
003 Product 3 1,000 1,200 1,100 1,200
004 Product 4 1,000 600 700 500
005 0
 
Ad

Advertisements

D

Debra Dalgleish

Instead of storing the items on a separate sheet, enter everything on
the Master sheet. Add a column to indicate the month of each entry.

At the top of the worksheet, use Data>Validation to create a dropdown
list of month names. A worksheet change event can be used to run an
Advanced Filter after a month has been selected from the list.

I've added a sample workbook to my site that demonstrates this:

http://www.contextures.com/excelfiles.html#Filter

Under the Filters heading, look for 'Summarize Budget Details' and
download AdvFilterBudget.xls
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top