VLOOKING- another excel document, multi worksheet

[KevinRamsay]

I am using a VLOOK to search another excel document - my question is can you seach multi workstations within that
excel file?

[Pete_UK]

Do you mean "workstations", or should that be "worksheets" ?

Can you tell us the cell containing the lookup value, the names of the sheets that you want to search in, the ranges where you expect the data to occur, the filename and path of the file ? (and any other relevant details). Perhaps you can post the formula that you are using now.

Pete

[KevinRamsay]

A have a file called Calander - I am doing a VLOOK to another file called Job Board
=VLOOKUP(B28,'[JOB BOARD 2013.xlsx]Active'!$B$2:$K$204,3)
- works great but the problem is that Job Board 2013.xlsx has multi worksheets
- I need to search each worksheet for a project number (the project could be remove to another worksheet)
- we have "2013 Completed" and "2012 Completed" worksheet which I need to search

[KevinRamsay]

=IFERROR(VLOOKUP(B29,'[JOB BOARD 2013.xlsx]Active Projects'!$B$2:$K$204,3), FALSE),IFERROR(VLOOKUP(B29,'[JOB BOARD 2013.xlsx]2013 Completed'!$B$2:$K$204,3, FALSE),IFERROR(VLOOKUP(B29,'[JOB BOARD 2013.xlsx]2012 Completed'!$B$2:$K$204,3, FALSE)

I thought with would work - what it doesn't

[Pete_UK]

Well that formula implies that you have the other file open at the same time, so we don't have to concern ourselves with the path. Try this formula:

=IFERROR(VLOOKUP(B28,'[JOB BOARD 2013.xlsx]Active'!$B:$K$,3,0),IFERROR(VLOOKUP(B28,'[JOB BOARD 2013.xlsx]2013 Completed'!$B:$K$,3,0),IFERROR(VLOOKUP(B28,'[JOB BOARD 2013.xlsx]2012 Completed'!$B:$K$,3,0),"not found")))

Note that I've changed the table references to full column (i.e. $B:$K), as I don't know how big those tables will be, but as you are only getting data from the 3rd column you could change them to $B:$D.

Hope this helps.

Pete

[Pete_UK]

[QUOTE=KevinRamsay;3446055I thought with would work - what it doesn't[/QUOTE]

Note that I've also used the fourth parameter in the VLOOKUP function, set to zero (or FALSE), which means to look for an exact match.

Also, your sheet name seems to have changed to Active Projects, rather than Active, and you are now using B29 rather than B28 as your lookup value.

Pete

[KevinRamsay]

Works great thanks- what do I need to do, the Job Board files is not all ways open

[KevinRamsay]

is this correct? just insert the path?

=IFERROR(VLOOKUP(B34,'[H:\11 Op System\Job Board\JOB BOARD 2013.xlsx]Active Projects'!$B$3:$K$500,3,0),IFERROR(VLOOKUP(B34,'[H:\11 Op System\Job Board\JOB BOARD 2013.xlsx]2013 Completed'!$B$3:$K$500,3,0),IFERROR(VLOOKUP(B47,'[H:\11 Op System\Job Board\JOB BOARD 2013.xlsx]2012 Completed'!$B34:$K$500,3,0),"not found")))

[Pete_UK]

If it was working in Post #7 then you don't need to do anything. If you close the Job Board file then Excel will automatically insert the path for you and that formula will expand in the Calendar file.

Hope this helps.

Pete

[KevinRamsay]

Thanks for all of your help. You made my day!!!!!!!

[Pete_UK]

Glad to hear it, Kevin.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete