Displaying data from seperate sheet upon drop down box entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keeper - 19 May 2006 16:07 GMT

I have nearly 20 sheets of data all different values of columns and rows and
would like to create in the first empty sheet a search menu. I would like
this first sheet to be capable of displaying data from another sheet by
selecting the sheet name, rows, and columns from drop down menues.

Ex. Sheet "A" has 4 columns and 40 rows. The first 20 rows belong to one set
and the second 20 rows belong to another. I'd like to use the first sheet
named "Search" to display all the data in sheet "A" belonging to the first
set (first 20rows and 4 columns) by simply selecting
Sheet = A
Columns = 1-4
Rows = 1-20

These selected from drop down menues.

Is this possible? No calculations are needed i simply need a way to call up
data easier than searching through each seperate tab.

Reply to this Message

Miguel Zapico - 19 May 2006 19:29 GMT

I would make 5 different dropdowns, one for the sheet, and two for each
range. With that, you can create a column header (let's say row 2) that goes
from the starting column to the ending column (values 1, 2, 3 and 4), a row
header (column A) that does the same with the rows.
Let's say that the sheet dropdown with the sheet name is in A1, you can use
the following formula inside this "table" (this is for cell B3)
=INDIRECT($A$1 & "!" & ADDRESS($A3,B$2))

Hope this helps,
Miguel.

> I have nearly 20 sheets of data all different values of columns and rows and
> would like to create in the first empty sheet a search menu. I would like
[quoted text clipped - 13 lines]
> Is this possible? No calculations are needed i simply need a way to call up
> data easier than searching through each seperate tab.

Reply to this Message