How to continue ranking across multiple sheets in excel
<p>I have data across multiple sheets in excel 2007 (will shift to higher version later) for which I want to make a Search option, so I used another sheet to enter desired input which in turn searches across all sheets and displays result based on RANK which is set in each individual sheets.</p>

<p>Eg:</p>

<p><a href="https://i.stack.imgur.com/uGFxm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uGFxm.png" alt="enter image description here"></a></p>

<p>Now I have all sheets with their individual ranking, but i need to have continued ranking across all sheets, below is expected result:</p>

<p><a href="https://i.stack.imgur.com/9c3jx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9c3jx.png" alt="enter image description here"></a></p>

<p>I am using below formulas for Rank &amp; Rating columns:</p>

<p>RANK => RANK(B3,$B$3:$B$65536,1)</p>

<p>RATING => SEARCH(Sheet4!$C$2, C3)+ROW()/100000</p>

<p>Thanks in advance.</p>