Formula to search multiple worksheets in workbook

I really don't know if this is possible (fingers crossed it is and its easy..LOL)

I have 150-200 clients. I am taking their YE financials and condensing them into a shorter "version". Each client will have their own worksheet in my Master. I want a formula that based on client ID would search the entire workbook (each worksheet) and pull the corresponding data depending on what I am looking for. It needs to match ID and then match up "acct".

I have attached a sample. My auto form is what I need filled out based on what ID I enter. I need it to search each sheet for the corresponding ID in A1 and then search for the approp Acct.


Thoughts??

try the formulas in the attached
note: some of the data in your excel file contained spaces at the end of the text so you might wanna recheck your original file also

WOW!! Ok thats impressive.

1. Can you explain what it means or is searching for so if I get an error I can trace it back?

2. My realy project has 150 on Master List and will have Approx 150 plus worksheets to search. Will this formula work for more than just the 5 in my sample?

3. Will space at the end of my text give me an error message?

Thank you again!! I am going to try and test it on my file.

ok...I have it working in without errors (granted its not pulling any information) but I am lost as to what part of the formula is it pulling from the various worksheets...I really ONLY want it to pull from A1 since the ID may be used in other places or come across as a $ amount in a different clients financials.

Here is a more detailed example of what I am doing

I can change the names of worksheets to match whatever I need to be looking for but would prefer to go by names in Alpha order.

Thanks

check the attachment.
There were also some inconsistent data in your worksheet that I fixed such as spaces at the end of the words and the sheet names.

here is some brief explanation about the used formula:

1. VLOOKUP(C$10,Master!$A$1:$B$999,2,FALSE) looks up the sheet names from the master sheet using the ID number
2. SUBSTITUTE(VLOOKUP(C$10,Master!$A$1:$B$999,2,FALSE),"'","''") replaces the single quotation with two single quotations in the sheet name because other wise excel wont accept it and give a reference error
3. &" "&C$10&"'!$B:$D" concatenates the ID number with the name to for the complete sheet name
4. INDIRECT function converts the text into cell references
5. the final VLOOKUP is to find your data in the different worksheets
6. the IFERROR function is used to insert blank when the data isnt found

Sorry to be a pain! But the attachment you sent doesn't seem to have anything on it other than what I sent.

Is the above explanation based on a new formula???

look closely man at C11:G17, there are formulas there, these were previously only numbers

Thank you, Thank you, Thank you!!

One last question....i got it working finally but it is returning information in column B on search worksheets and I need column D. Where in the formula can I change that?

Thanks again!

it actually returns information from column D in the worksheets not B!

Ok well when I put the formula in my actual workbook, not the sample, it is returning values from B not D. I don't know what aspect of the formula is returning the values so i don't know how to change it.

Thanks

Correction: Its returning C not D.

Nevermind....fixed it!! Thanks!

hi guys! Newbie here, i've been reading the thread and learning from it little by little.

Thanks you guys and more power.