Find a value across multiple sheets in another workbook

[bobs1]

Hi,

Ive done a lot of reading over this forum for the topic but none of them have a solution that works.

Essentially i have a new spreadsheet (Contract Analysis) that summaries data from multiple years. Each year is kept in its own spreadsheet (Sale Orders_2008, Sale Orders_2009 etc) with multiple worksheets, 12 of with have monthly data (SalesJan, SalesMar, SalesDec etc). I made a post asking if there was a macro to search each spreadsheet across those 12 worksheets but i didnt get a response so i'll make a simpler request.

Is there a formula/macro that will search a particular workbook (ie Sale Orders_2009) across the 12 worksheets (Sales Jan.... SalesDec) in the data range (B10:I34) where column B contains the value to search for and column H contains the data to copy, the value to search for is in column A of Contract Analysis and paste it in column G of the Contract Analysis workbook.

I was able to modify a function to do this, but it requires the spreadsheet to be open to pull the data:

Function CLOOKUP(lookup)
    arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    For Each entry In arr
        With Workbooks("Sales Orders_2009.xls").Sheets("Sales" & entry)
            For i = 10 To 34
                If .Range("B" & i).Value = lookup Then
                    CLOOKUP = .Range("H" & i).Value
                    GoTo complete
                End If
            Next i
        End With
    Next entry
complete:
End Function 

But using this method requires me to have about 6 different spreadsheets (one for each year) open in order to work on the Contracts Analysis spreadsheet

please help!

[karan]

You have given a pretty good explanation of what you want to achieve.

But, it would be great if you could attach a sample workbook demonstrating some class sheets and a mockup of the output sheet you want?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Regards,
Karan

[bradles]

Is there a formula/macro that will search a particular workbook (ie Sale Orders_2009) across the 12 worksheets (Sales Jan.... SalesDec) in the data range (B10:I34) where column B contains the value to search for and column H contains the data to copy, the value to search for is in column A of Contract Analysis and paste it in column G of the Contract Analysis workbook.

Can you give us an example of what the value is that you are trying to lookup and what relationship it has (if any) to the workbook/worksheet it needs to look up.

Brad

[bobs1]

Ok ive done up 2 mock excel spread sheets. On the contract analysis one you see tabs for customers. What i would like to happen is when you type an invoice number, it will search the 12 month worksheets in Sales Orders_2010 for the invoice number and then pull the US$ invoice amount through and paste it into the Invoice amount in Contract Analysis

[bobs1]

Am i asking a bit too much?

[bradles]

Hi Bobs,
I'm sure what you're asking could be done...eventually. I hope you don't mind me saying so, but while what you've setup looks nice, the data doesn't appear to be structured functionally the way you want to work with it. The invoice date should drive this for you.

I've attached a sample of a way in which this data can be structured in order to give you greater flexibility.

I've consolidated all of the Export Sales data onto a sheet called "ExportSalesData". Note that the data is stored from the top left - eg, there is no row gaps between column headers. This is how data truly wants to be stored ;-)

This allows you to easily run all sorts of reports on the data as it is structured more like a database, especially when you use a dynamic range like the one I've used called "ExportSalesData" - see menu option INSERT | NAME | DEFINE to see the actual range formula I used.

The simplest report you can run from having data setup like this is a Pivot Table. See the many I have setup on separate worksheets following the data worksheet, including:

"Export Sales by Customer"; and
"Export Sales by Quater 2010"

The beauty of this is that you only need to right click your pivot tables and select refresh to get the latest update of any data that you add to the end of your data on the "ExportSalesData" worksheet.

The pivot tables might look nice, but they are nothing fancy, I only added an Auto Format from the Format | AutoFormat... menu option - a one click wonder.

You'll notice on the "ExportSalesData" worksheet, I've added auto filters. From here you can do exactly what you were asking. Drop down on the Customer filter and select Customer 1. Next select the Invoice dropdown and select CUSTOM and enter 100506 as the invoice number. Click OK to view only that invoice for that Customer.

If you want to do it the way you originally stated, check the "Query Customer Invoice" worksheet where you can enter "100106" in the Invoice cell A3 and "Customer1" in the Customer cell C3. This will show the amount of that invoice in the Amount field in cell B6.

By the way: I added the FX amount (0.9135) as a named constant called FX and included it in the formulas in column H on the "ExportSalesData" worksheet.

I hope this helps.

[bobs1]

i completely agree with you bradles as i had this discussion about how to layout the source data with my supervisors at the start. but theyve being doing it this way for years, with an array of formulas and trying to consolidate the real spreadsheets like youve done would take a long time and they arent willing to do that atm