Lookup function for multiple worksheets

Apologies if this is in the wrong forum...

But I require some assistance, I have been trying for the past several hours for a solution, but I seem to be missing something.

I am trying to create a VLOOKUP function which searches multiple sheets.



The VLOOKUP for the individual sheet is (this works pefectly):

=VLOOKUP(A479,'iWc 23-05-11'!$B$3:$N$30,13,FALSE)




However, I have 52 different Sheet names off which I want to search from. The layout/cells are exactly the same on each sheet, however the reference is unique.



I have searched the net and keep coming up with this function:

=VLOOKUP(A479,INDIRECT("'"&INDEX(A1:A52,MATCH(TRUE,COUNTIF(INDIRECT("'"&A1:A52&"'!B3:N30"),A479)>0,0))&"'! B3:N30"),13,0)



Basically, putting all the sheet names in a seperate sheet called 'INDEX'.

The above function does not return a value and seems to cause an error.

I hope I have explained everything clearly as to what im trying to acheive, Im hoping its something simple which I have missed.

The 52 sheets are weekly sheets for the year.

Your help would be truly grateful.

Regards

Matt

Thanks for you response, appreciated,

Would you say this is the best method for what i after or is there another way without having to press control, shift and enter each time?

regards
Matt

This looks like just the answer I am searching for. I have copied the formula and created an additional sheet called index with a list of the spreedsheet names but it is coming up as a #REF error. Can you see what I am missing in the attached test file ?

Cheers
Rhys

sure, i didnt realise

I only saw the first line of your post in my email notification, didnt realise you had answered my question. Thank you for that. Still a bit confused re how to include the sheet names in the index sheet (have renamed it to' list' as i see your point re confusion). I assume there is a syntax to tell excel that you are referring to a sheet when you write the name in as all I had was the name but excel wouldn't know I was referring to a sheet and would just see it as text but I'm almost there now

Thanks a lot
Rhys

At the risk of dragging on in someone else post I am very appreciative of your help but am now getting both #ref and #n/a responses to the same formula code. I just can't see what I'm missing in the attached and why it won't pick up the numbers in column E in each of the sheets named Week 1,2 etc and pop them in the first sheet. I'm sure it is something quite simple. Can you point me in the right direction ?

cheers
rhys

I did try on the individual formula cells but nothing happened. Then looking at the example referred to in your signature I tried it across the B1:B3 in Sheet1 and got it to put the braces on but still #ref and #na errors. then I tried it again across the three rows in column B using the following variation of the formula

=VLOOKUP(A1:A3,INDIRECT("'"&INDEX(list!A$1:A$54,MATCH(TRUE,COUNTIF(INDIRECT("'"&list!A$1:A$54&"'!a1:e30"),A1:A3)>0,0))&"'!a1:e30"),5,0)

ie changing the lookup from the cell in the same row to the range and it did put the braces on but still the errors. Very confused !

That is very helpful, thanks. Interesting point I have just discovered and part of the reason I have been struggling with this is that the sheet you have fixed works fine on my work pc with excel 2010 but at home I'm using 2007 and instead of showing the solution the formulas in the very same workbook are #ref errors (saving as excel workbook doesnt help). Strange that the compatibility issue is so significant. Should I be upgrading to 2010 at home ? If I hadn't been playing with it at work I would never have realised that the version is causing such a problem

Thanks so much for your help, I have got it working and your advice is going to save me so much time. I really appreciate it. One last question, I don't suppose there is any way to create a wildcard for the list of sheets. ie some of the sheets will be automatically created with a constant intro and then a number that changes with each instance. eg sample1222, sample1287, sample1322 etc, etc. Is there anyway to create a single entry that will cover any sheets that start with sample regardless of the numbers that follow ?
Cheers
Rhys

I did think I might be pushing it on that request. Thanks again for your help getting this set up, it is such a relief not to have to manually enter all the information. Re the wildcard possibility I'll start a new thread, not hopeful but you never know

Cheers
Rhys