Formula to search sheet, find value, then return another specific cell at top of worksheet

[KH1701]

Hi,
I have many worksheets, one summary page.
All the sheets have different job numbers. To find the job numbers I need, I have used VLOOKUPS.
I need a formula that searches all the spreadsheets (12), and returns a specific cell for the spreadsheet that has that job number in it to the summary page. this specific cell is not in line with the job numbers.
eg. each spreadsheet has a SET NAME in cell B1. underneath this is a 2 column table which is the job number, column A and description column B. so A3:B76
I need to return the value in B1, once the IFERROR VLOOKUPS find the job number in column A. I then need to apply this to each other worksheet I have (so use an IF ERROR).
The B1 Cell is designated on each worksheet, and will not change.

Only if such a thing can happen.........

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).