[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Francis
General User

Joined: 26 Apr 2006
Posts: 12

 Posted: Wed Apr 26, 2006 2:00 am    Post subject: vlookup Over Multiple Sheets I've compiled a document with 13 sheets, the first for searching, the next 12 for data for each month of the year. The search sheet will contain multiple vlookup functions. I have been trying to get a vlookup function to search across these multiple sheets by using an if function - if the vlookup result is nothing on the first sheet, have a look at the next and so on. But I can not seem to get it to work. Am I going about this the wrong way? Is there a function that would make these easier? Any help would be appreciated.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Wed Apr 26, 2006 2:39 am    Post subject: I don't know of such a function. Rather than use one huge nested IF() function, at least to build up the logic you require in manageable chunks that you can fault find, I suggest: 1. Put the VLOOKUP() for the January(?) sheet in one cell, the VLOOKUP() for the February sheet in the next cell, etc., let's assume B2, C2, etc. 2. If the VLOOKUP() doesn't find what you want in January, it returns #N/A, so you can use the function ISNA() in an IF() function, e.g. in the cells above the VLOOKUP()s, to identify which VLOOKUP() found what you wanted, e.g. in B1: =IF(ISNA(B2);"Not Found";"Found") and fill across 12 columns. 3. Provided you just want the first find, then use HLOOKUP() across the range of Found/Not Found and the VLOOKUP()s, e.g. if the first Found/Not Found is in B1, the last VLOOKUP() in M2: =HLOOKUP("Found";B1:M2;2;false) (I admit I haven't tried this, so if you can't make it work, post again.) Also, I think there is a limit to how many IF() functions you can nest, and that it might well be less than 12. I think the step by step is more likely to work, and more likely to work without loads of head scratching.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Apr 26, 2006 3:25 am    Post subject: Splitting data across many sheets and files is common (but bad) practice. Keep everything in one single unsorted list and let the program do the separation by time-intervals. Use filters, pivot-tables(data pilot), sheet-functions (YEAR,QUARTER,WEEK,MONTH,DSUM,DMAX,DCOUNT,D...) consolidation and subtotals. (A database can do even better). Anyway: You may define a named range including 12 ranges separated by semicolon: myMonthLookup =\$Jan.\$A\$1:\$D\$65536;\$Feb.\$A\$1:\$D\$65536;...;\$Dec.\$A\$1:\$D\$65536 Then you may lookup some value in third column of Dec like this: =VLOOKUP(value;INDEX((myMonthLookup);0;0;12);3;0) Read the help on INDEX() function. INDEX((reference);row_Index;col_Index;SubRange) (myMonthLookup) needs braces in this case col_index 0 means: no specific column (missing argument) row_index 0 means: no specific row (missing argument) SubRange 12 is the 12th range in your range-list.
Francis
General User

Joined: 26 Apr 2006
Posts: 12

 Posted: Wed Apr 26, 2006 3:47 am    Post subject: Thanks for the quick reply and helpful tips. Unfortunately the search sheet with all of the vlookup functions in is designed to be printed and contains about 30 different entries. What I've thought about doing is to construct another sheet which contains all of the data, for the whole year and use MONTH to extract the individual monthly data and insert it on the monthly sheets. This way I can use VLOOKUP on the one sheet, the monthly sheets will be there just for show. Thanks again.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Wed Apr 26, 2006 4:20 am    Post subject: Re: vlookup Over Multiple Sheets You might try plain LOOKUP(). .... =LOOKUP(D1;Sheet2.A1:Sheet2.A3;Sheet3.A1:Sheet3.A3) For example; if sheet 2 has A,B,C in column A, and sheet 3 has X,Y,Z in column A, and if sheet 1 cell D1 has "B", the statement looks up D1's content in sheet 2 [second row entry is a B] and returns the same row [second] content from sheet 3. That is, sheet 1 cell A1 [or wherever the formula is placed] will show a Y. David.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Apr 26, 2006 4:23 am    Post subject: When filtering by months you can redirect the output to another sheet (see options of Standard-Filter dialog), creating your month-reports on the fly. Another hint on using filters: http://www.oooforum.org/forum/viewtopic.phtml?t=35536
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Apr 26, 2006 4:35 am    Post subject: Just a short description how to use the data pilot: Having your complete list with all data in columns A to F and dates in colun A, add two columns: G1: Year G2: =Year(\$A2) E1: Month E2: =\$G2&TEXT(MONTH(\$A2);"00") drag down G2:E2 Select A to E Menu:Data>Pilot Target-Cell : A1 of some unused sheet. Other options: ON (they won't hurt ) Drag Month and Year to "Page Fields" Other Fields to "Row Fields" If you want to show an aggregation of some field (SUM, Count or alike) drag the field to "Data Field" Play around with the resulting table . You can drag the grey boxes on the sheet. Make 11 copies of the sheet and select a different month from the Month-dropdown.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum