Search multiple tabs for number in string

Hi

Hoping someone can assist me with the attachment. Have searched but unable to find exact solution. Have tried on the attachment but getting Ref.

What I am looking for is a formula that will search Column I to find the following and pull just the number, problem is that this data is on various rows!

"- 0 non qualifing agreements"
"- 43 qualifying agreements"

my problem is that there is no header for the data in column A on the individual tab sheets.

There are 88 Tab sheets all with a unique number name.

I have managed to pull using the tab name and the exact row as shown on the attached using this formula,

=VALUE(MID('1015541'!I80,SEARCH("QUALIFYING",'1015541'!I80)-3,2))

but I would prefer to use the formula below as my report has many more sheets and I have to do this for 4 quarters for 2 years!

=VALUE(MID(INDIRECT("'"&$A3&"'!$I:$I$"),SEARCH("QUALIFYING",INDIRECT("'"&$A3&"'!$I:$I$I"))-3,2))

If I am heading down the wrong route to solve my solution, I would appreciate any assistance to sort this out.

Many thanks
Caroline

With this I tend to go to VB.
This solution is straight forward. Extract the numbers in the worksheet by using mid() function.

Please Login or Register  to view this content.

Thank you for the reply.

I have never used VBA before! Have pasted it into vba worksheet "totals" but nothing seems to be happening.

Please help.

Thanks

Press Alt-F11
Insert a module
Copy Paste the Code
Put the cursor somewhere in the code (between 'sub' and 'end sub'
Press F5

Thank you so much for helping me.

Works perfectly.