newparadigmz

<div>
For the green cells use<br><br>=INDIRECT(ADDRESS(INT(ROW()/5)+1,2))
</div>


<div>
WOW THANKS!<br><br>Can you give a quick (or not quick) explanation of how it works? In particular, what is the relevance of the values 10 and 5 in the formulas? What needs to be tweaked as I move pieces around?
</div>


<div>
5 is the row spacing between the teams<br>10 is the first row where the teams start to be listed.
</div>


<div>
Ah, just to clarify, is that for the INFO or REPORT section?
</div>


<div>
Another option using OFFSET function, see attached.<br><strong>Sheet 1</strong><br>Cells D2 to D4 use the ADDRESS function with MATCH to determine address of team name (column B) in column F.<br><br>The OFFSET in each of the yellow areas then uses that as the starting point for getting data. The syntax of OFFSET is:<br>=OFFSET(Reference, Offset Rows, Offset Columns, Width, Height)<br><br>Reference - uses INDIRECT to return contents of D2 to D4 in which the reference is generated by ADDRESS above,<br>Offset Rows and Columns is then increasing by 1 each row and column to locate the next cell in the team data area such as the matrix below: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<table style="margin-left: calc(26%); width: 14%; margin-right: calc(61%);"><tbody><tr><td style="width: 29.7068%;">1,1</td><td style="width: 40.0463%;">1,2</td><td style="width: 29.5525%;">1,3</td></tr><tr><td style="width: 29.7068%;">2,1</td><td style="width: 40.0463%;">2,2</td><td style="width: 29.5525%;">2,3</td></tr><tr><td style="width: 29.7068%;">3,1</td><td style="width: 40.0463%;">3,2</td><td style="width: 29.5525%;">3,3</td></tr></tbody></table><br>Width and Height - both set to 1 to produce a single cell range.<br><br>The references in column D can be hidden if so required; hide column or white text.<br><br><strong>Sheets 2 &amp; 3</strong><br>This has replicated what I think you're referring to with team data being on a different tab.<br><br>The report is on sheet2 and the team data is moved to sheet3 and I have changed the MATCH and ADDRESS function in the cells D2 to D4.<br><br>In your real file may still need to change:<br>MATCH - change the column in which the team names are held (currently $F:$F)<br>ADDRESS - the column number to the column number of the team names (currently 6) and change the sheet name (currently "Sheet3").<br><br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w22/1562200/OFFSET-Data.xlsx" rel="ugc">OFFSET Data.xlsx</a>
</div>


OFFSET-Data.xlsx

<div>
Another option, using the other form of INDEX where you can specify multiple areas.<br><br>See sheet4 of attached (same file as earlier but additional sheet).<br><br>INDEX Multiple array syntax:<br>=INDEX((Array1,Array2,Array3),Row,Column,Area)<br><br>Arrays - have to be enclosed in brackets to identify as arrays rather than next parameter in the function.<br>Row, Column - using matrix like earlier,<br>Area - this chooses which array to get data from; in the file I have used a MATCH function on a list of names to determine team array area; I am guessing AA, BB, CC are not real team names so might be better to use a cell range with a list of names rather than hard-coded into the formula.<br><br>With this version, the team data can just be cut and pasted onto a different tab and should still work correctly.<br><br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w22/1562202/OFFSET-Data.xlsx" rel="ugc">OFFSET Data.xlsx</a>
</div>



The blue section is static info about teams that will sit in another tab. The orange section will be automatically sorted by score and meant to be refreshed and changing in order. I am looking for formulas to put in the green and yellow sections that will always show the contents of the blue sections accordingly to the teams that are in 1st,2nd,3rd place.
I cannot upload an actual sheet so would greatly appreciate just some sample formulas!

Need Excel formulas to lookup ranges of text based on changing order of rank

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.