Excel 2007 : Using drop down menu's as a filter tool, but having a seperate sheet pulling data

[Dequadin]

Hey everyone,

Im new to excel, and this forum, but i've spent some time goofing around playing with some tools, but it has gotten to the point where i need some assistance
Im hoping someone can help me out as i've reached my excel skill limits

The format im trying to achieve mimics a standard "logistics" point of view. simply tracking items that have been requested for shipment.

What i've done to start off is. "sheet1" is my "request form" and once someone wants to edit this sheet, you will automatically be given a reference#. this reference number is setup in a date format (eg. 4/25/2012 9:41), this is a multi-purpose tool as it also works as a timestamp of when the request was issued.

My question is:

Within (sheet1)"request form" - I have multiple questions, and depending on the first question asked, future questions are altered depending on what data has been selected.

example:

Agency: (drop down selection)
"please select from the drop down menu above, the agency needing new hardware"

Set Number: (drop down selection) <-- depending how they answer the above question, the set #'s alter accordingly
"please indicate what set from within the agency has requested new hardware"

Category: (drop down selection)
"please select the type of request. example ( consumables, technology, graphics, general)"

Item Requested (drop down selection) <-- depending how they answer the above question, the specific item changes to match the category
"please select the specific item required"


Anyways, I hope this is enough information to help you understand my format.

Within Sheet2 I have created multiple tables. they are broken down into 4 major categories "consumables, technology, graphics, general" each category holds 25 tables, broken down in a monthly manner "Jan - Dec " (this reflects the different "set #'s)

Is it possible in Sheet2, to automatically (depending on the data selected in sheet1) pull that specific information, and display them in a numerical value "1+1+1" in my tables?

It sounds like i would be using a lot of "IF" values, and depending on what value is selected it would place it within sheet2's specific table.


I appologise if this makes no sense, but if it does! i would really appreachiate your input.

Attached i have a rough outline of what my purpose wants to look like.

ps. right now in Sheet2, those value's are just manual data entry. the end goal is to pre-populate depending on the form request

Cheers