Order History Sheet: Lookup all sheets in the workbook

Hey!

Ran in to a bit of a problem with finding information from multiple sheets in the same workbook.

PREFACE
Every month we have an order sheet that's generated specifically by the store for that particular month. That sheet is sent to me for record keeping purposes which I aggregate into one workbook while renaming the sheet name to the month it belongs to. So as you can imagine the workbook has sheets for every single month of the year for up to two years.

Each of these sheets has the exact same headers and we use named ranges for each of the columns (ITEM_NAME, QTY, TOTAL, DATE, CAT). These named ranges are the same across all sheets but the scope of the range is restricted to that particular sheet. So that way, Sheet1, Sheet2, Sheet3 can all have the same named ranges.

REQUIREMENT
I need a sheet at the start of the workbook, where all I have to do is type in the item name and it retrieves the corresponding data from across all the sheets in the workbook (there can be multiple instances within the same sheet). It must present the data based on the most recent month to the oldest month and also order it from the last row to the top row.

EX:

Sheets Present: Aug, Sep, Oct
The data should be presented in the order of:
Oct: Last row to first row -> Sep: Last row to first row -> Aug: Last row to first row -> and so on.

Since new sheets are added every month, the formulas need to be able to pick that up (maybe from a helper sheet / column) and lookup the values accordingly.


I've attached a sample sheet which should make it easier. Thanks for any help.

Hope this can be achieved with just formulas, but in the event it cannot, then VBA is ok

Wow that's awesome. Thank you.

Couple of questions
# I wasn't able to figure out how you targeted the specific row in the FIND Sheet to output the value.
# If I Input the same name again, the value repeat. Is there any way to keep that from happening? As in clear the results and then update them again?
# Lastly is there any way to do this using Named Ranges instead of addressing the columns directly? The columns in my actual sheet are not contiguous and there could also be an addition of columns in the newer sheets in between existing columns that will cause an issue while retrieving the data if the columns are absolute.

Hahaha... Epic Title!

This stuff is insane. I'm going to be staring at it all night trying to figure it out

Thank you very much


I'd like to explore the VBA approach if it's ok with you as it seems a lot less resource intensive.

# Need a way to ensure that the latest order history is always at the top (Sheets can be named in the MMM-YYYY format (Aug-2015) or mm-yyyy format (08-2015), if that's what is needed for VBA to ensure it goes through the sheets correctly. I don't know if arranging the sheets in order will help with that, but if does, then that's not a problem to do. A helper sheet if that makes it easy is also cool.

# Reference "Named Ranges" instead of columns as the newer "data sheets" can have the columns mixed up (not very likely, but it could happen if the store decides to add additional product data to the sheets). Also my actual sheet will not be retrieving entries from contiguous columns in the "data sheets" (Ex: ColB, ColD, ColG - you get the idea)

# If Cell
F1 = "ALL" then it picks up all the data,
F1 = "DEBITS" then only negative values,
F1 = "CREDITS" only positive values

# Clear the results if the input field is cleared