Vlookup across 12 worksheets

[bobs1]

Hello,

I've being asked to create this awesome new spreadsheet that summaries data from two other spreadsheets. I personally have no idea about excel but have gotten quiet good with the vlookup and isna formulas etc and so far so good.

Till i had to search for a value over 12 worksheets. Ive tried Vlookup using an indirect formula using named cells etc i found but i cant work it out and all this reading has turned my brain to mush.

So what im trying to do is:

I have a new spreadsheet called summary. What i need to do is pull sales data through from a spread sheet called "Sales Orders_2009". In the spread sheet there is multiple tabs, 12 of which i need to search. The tabs are called SalesJan, SalesFeb etc. On each tab i need to search in the data range B10 to I34. In the Summary spreadsheet i type an invoice number into A2 and that is the lookup value to search for across the 12 worksheets.

Any help would be muhcly appreciated.

[teylyn]

Hi bobs1, welcome to the forum.

Do the 12 sheets have the same structure? You could then consolidate them into one sheet and do the vlookup on just that consolidation sheet. Other than that, you could do something like

=if(isna(vlookup(a2,SalesJan!B10:I34,2,false)),0,vlookup(a2,SalesJan!B10:I34,2,false))+if(isna(vlookup(a2,SalesFeb!B10:I34,2,false)),0,vlookup(a2,SalesFeb!B10:I34,2,false))+ ... +if(isna(vlookup(a2,SalesDec!B10:I34,2,false)),0,vlookup(a2,SalesDec!B10:I34,2,false))

Not pretty, but it should work.

[BergShorn]

I saw this function at http://www.excelforum.com/excel-gene...e-sheets.html:

Please Login or Register  to view this content.

Then you could use this:

Please Login or Register  to view this content.

[bobs1]

Hi Teylen,

Unfortunately you can only go up to 5 vlookups in a formula before it says its too long

BergShorn,

ill have a look into it but i really dont understand macros at all. I have no programming background

[bobs1]

Thanks for the help, i was able to use this macro to do the job. Thought id list it here in case it can help sombody else

Function ALOOKUP(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
ALOOKUP = .Range("H" & i).value
GoTo complete
End If
Next i
End With
Next entry
complete:

End Function

[shg]

Thanks for posting back.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.