Place reference into Excel formula to create dynamic index
Dear Experts
I have an index formula to list out the top 10 users which works perfectly: =INDEX(SORT(S2:U123,2,-1),SEQUENCE(10),{1,2})
But the list of users changes each week so S2:U123 isn't always the full range, so I have a number formula to determine the last cell with data in the S2:Uxxx range - this also works fine in isolation ... ="S2:U"&LOOKUP(2,1/(S:S<>""),ROW(S:S)) & produces the right 'text' in a cell.
What I'd like to be able to do is combine the two formulas so the range in the Index formula is automatically calculated.
I have tried the following but no luck:
Attached is an example if that helps
Please note the data in columns A-E are created by a vlookup formula (The results have been hard coded in the example) - I say this as I also wonder whether the solution might be to create the Index formula a different way so it can deal with null values from A-E thereby skipping the need to combine the formulas
EE Combining formula.xlsx
I have an index formula to list out the top 10 users which works perfectly: =INDEX(SORT(S2:U123,2,-1),SEQUENCE(10),{1,2})
But the list of users changes each week so S2:U123 isn't always the full range, so I have a number formula to determine the last cell with data in the S2:Uxxx range - this also works fine in isolation ... ="S2:U"&LOOKUP(2,1/(S:S<>""),ROW(S:S)) & produces the right 'text' in a cell.
What I'd like to be able to do is combine the two formulas so the range in the Index formula is automatically calculated.
I have tried the following but no luck:
- Putting it directly in: =INDEX(SORT("S2:U"&LOOKUP(2,1/(S:S<>""),ROW(S:S)),2,-1),SEQUENCE(10),{1,2})
- Referencing a futher cell (V3) to put the reuslt in: =INDEX(SORT(VALUE(V3),2,-1),SEQUENCE(10),{1,2})
Attached is an example if that helps
Please note the data in columns A-E are created by a vlookup formula (The results have been hard coded in the example) - I say this as I also wonder whether the solution might be to create the Index formula a different way so it can deal with null values from A-E thereby skipping the need to combine the formulas
EE Combining formula.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow thats brilliant, works a treat - thank you so much
You could also create a dynamic named range or Data Table for the source data.
Both would adjust automatically as data grows.
Dynamic Range
Use the OFFSET function to create a range "DataSource"
Syntax:
=OFFSET(Reference Point, Row Offset, Column Offset, Height, Width)
=OFFSET(Sheet1!$S$2,0,0,COUNTA(Sheet1!$S:$S),3) (replace Sheet1 with sheet name)
Use reference to "DataSource" instead of "S2:S123"
Data Table
Select the existing data in columns S to U (don't select whole column or any extra rows) and press Ctrl + T, this will bring up the Table creator wizard; tick the box to confirm that the data has headers. When Table is created and cursor is within the table there will be an extra tab on the Menu Ribbon, in that tab you can rename the Table. Easiest way to include it in the formula is to put formula in edit mode, delete the reference to S2:S123 and select the table; it will show as Table1[#All] (Table1 will show as Table Name if renamed as suggested).
=INDEX(SORT(Table1[#All],2,-1),SEQUENCE(10),{1,2})
Both would adjust automatically as data grows.
Dynamic Range
Use the OFFSET function to create a range "DataSource"
Syntax:
=OFFSET(Reference Point, Row Offset, Column Offset, Height, Width)
=OFFSET(Sheet1!$S$2,0,0,COUNTA(Sheet1!$S:$S),3) (replace Sheet1 with sheet name)
Use reference to "DataSource" instead of "S2:S123"
Data Table
Select the existing data in columns S to U (don't select whole column or any extra rows) and press Ctrl + T, this will bring up the Table creator wizard; tick the box to confirm that the data has headers. When Table is created and cursor is within the table there will be an extra tab on the Menu Ribbon, in that tab you can rename the Table. Easiest way to include it in the formula is to put formula in edit mode, delete the reference to S2:S123 and select the table; it will show as Table1[#All] (Table1 will show as Table Name if renamed as suggested).
=INDEX(SORT(Table1[#All],2,-1),SEQUENCE(10),{1,2})
Open in new window
As a rule, I don't like to reference entire columns in array type formulas. Doing so can sometimes make Excel recalculate slowly, and thereby result in an annoying delay each time you hit Enter.