OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

vlookup Over Multiple Sheets

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Francis
General User
General User


Joined: 26 Apr 2006
Posts: 12

PostPosted: Wed Apr 26, 2006 2:00 am    Post subject: vlookup Over Multiple Sheets Reply with quote

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.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Wed Apr 26, 2006 2:39 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 26, 2006 3:25 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Francis
General User
General User


Joined: 26 Apr 2006
Posts: 12

PostPosted: Wed Apr 26, 2006 3:47 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Apr 26, 2006 4:20 am    Post subject: Re: vlookup Over Multiple Sheets Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 26, 2006 4:23 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 26, 2006 4:35 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group