Index/match problem with Changing Rank - Example inside

**kamelkid2** · 01-23-2013, 09:29 AM

weighting example.xlsx

ok so i couldnt come up with a better title than this but i have come across a weird problem that i do not know how to address.

i have uploaded a simplified version for your review

background: i have put together a big fantasy baseball spreadsheet (nerdy, i know) and have a summary worksheet with the overall rank. on this summary worksheet, you can enter the fantasy team for who drafted that player, and it will input it into the worksheet that actually does the calculations to be used for counting stats per team for the players drafted - meaning it will count all the HR for players drafted by team 1, team 2 etc. this is used for determining who had the better draft and what statistical categories you need to improve upon.

on the setup page, however, you can put a weight factor into each of the stats and, if you change the weight factor with names already in the summary worksheet, it will change the rank of the players but the teams of who drafted them will not tag along with the reshuffle.

i have done this all without VBA but i cant think of a way to do this without it (nor can i think of a way to do this with it as im not a very good programmer). this is really the last hurdle i have on this thing as far as i can tell. If i have to use VBA i will but i would prefer functions.

the example sheet would make it more clear.

thank you for anyone willing to help...im pretty desperate here

weighting example.xlsx

**Brumbot** · 01-23-2013, 12:17 PM

So the problem is that the partner group in the summary box does not change along with the name group? Or that the partner changes in the calculation box when the rankings change? Both seem fixable but I'm confused as to what exactly the problem is.

**kamelkid2** · 01-23-2013, 02:45 PM

the main problem is that the partner group does not change in the summary box when the rankings change. I was able to get a fix for this but it basically involved having to calculate everything twice so im still looking for a more efficient solution

**Brumbot** · 01-24-2013, 04:43 AM

Originally Posted by kamelkid2

the main problem is that the partner group does not change in the summary box when the rankings change. I was able to get a fix for this but it basically involved having to calculate everything twice so im still looking for a more efficient solution

So the problem is with the circular reference created when you try to index match the summary? I will look at it.

EDIT:

The only way to do it without creating a circular reference is to make the partner names static in the calculation box, and do an index match from the summary box on partner names. Like so: