Formula to Locate Matching Data in Worksheets and return Yes or No

[hammer2]

Hi Folks

Need a formula to satisfy some criteria and then to lookup other sheet/s and return Yes or "" to a cell.

I have attached a sample Workbook.

Cheers
hammer

[igormigor]

Hi Hammer,
Not sure if I've understood fully what you want the calculation to be, but try with this formulas:

- to get TrA_Rank value in column P2 copy this formula in the cell and copy down the cell till the last needed row

Please Login or Register  to view this content.

- to show "Yes" or "" in column P2 copy this formula in the cell and copy down the cell till the last needed row

Please Login or Register  to view this content.

[hammer2]

Hi Igormigor
Thanks for the response
The 2nd formula is the one but I would like to be able to pull it across 57 Cols in all
As it is at the moment I would have to manually change the next one A:D"),4,0 to A:E"),5,0 etc
Hoping to get a formula that will pull across?
Thanks
hammer

[igormigor]

Check this formula. Based on the TrA_Rank value in column L (if its 1) you are displaying the value in column P.
After that I presume that you have same relative position for JoA_Rank, aDS_Rank and bDS_Rank, for both ranges M-O and Q-S.
Let me know if you have some issues.

Please Login or Register  to view this content.

Watch out, the formula will work if the values in columns ZZ-AAF are empty.

[hammer2]

Hi igormigor
I've put the formula in Col P just to explain the situation-
In the real workbook the headings begin with TrA_Rank as shown in Col L and continue to BO inclusive-

The formula would actually start in BP and be pulled across to DS inclusive.

So this is the formula I currently have in BP:

Please Login or Register  to view this content.

This works correctly but will not pull across-
The next cell in col BP would have this formula

Please Login or Register  to view this content.

and so on but I would have to do them all manually- was hoping to avoid that.
Thanks
hammer

[JohnTopley]

If I understand your requirement

Try

=IF(AND(J2=1,L2=1),IF(VLOOKUP(C2,INDIRECT(I2&"!A:BG"),COLUMNS($P:P)+3,0)>=10,"Yes",""),"")

[hammer2]

Just had to change $P:P to $D:D to reflect the real Workbook and we are underway

Many thanks to both igormigor and John for your wonderful assistance!
Regards
hammer

[JohnTopley]

FYI ... a little "neater"

=IF(AND(J2=1,L2=1),IF(VLOOKUP(C2,INDIRECT(I2&"!A:BG"),COLUMNS($A:D),0)>=10,"Yes",""),"")

starting 4th column in VLOOKUP range

[hammer2]

Very good!