Conditionally linking cells in Excel 2007

Question

I have a series of worksheets in an Excel spreadsheet. Each worksheet details a set of requirements, along with each component necessary to make that requirement happen, broken down into the following layers: UI, backend, services, database, and misc.

So columns like this:

Req#, Description, UI, Hours, Backend, Hours, Services, Hours, Database, Hours, Misc, Hours

Now I am trying to selectively take some of this data and place it into another worksheet to specifically detail what all has to be done in a given layer, for example the Database layer.

So I would select only the Database (and related hours) column, and link to them in this new "Master Database" worksheet. I don't know how to do this in Excel.

For comparison though, here's what I'd write as a SQL query:

SELECT Database,DatabaseHours 
FROM ReportsTable,FeedbackTable,AdminTable 
WHERE Database IS NOT NULL;

I just don't know how to make these relationships very easily in Excel.

Answer 1

I would look into using Pivot Tables. If you have all your data in an Excel sheet, creating a pivot table on another sheet will allow you to summarize data however you want.