Excel Game Formula Help

[KDavidP1987]

Hey all,

Context:
I am in the process of developing a learning game for a training class at work. It's based on the concept of the Around the World game we have likely all played with a Basketball.

In the game, players/teams move forward or backward depending on their ability to answer questions from the content.

Most of the formulas and VBA for this game work, but having particular issues with 1, which is changing itself whenever the cell is moved.

Question:
I have a column which is supposed to keep track of where the teams are on the board, using a INDEX(MATCH()) formula. The formula works perfectly! However, when the player's icon (cell) is moved forward or backwards the formula reorients itself, so that it's always showing them being on START

I tried using ABSOLUTE cell references, but it still changes. Is there any way around this?

Below is the code for the Blue Team/Player: Each team/player has code matching their color, the named cell reference.

=INDEX($A$2:$O$15,1,MATCH("Blue",$A$4:$O$4,0))

Once I move the cell, it changes to:

=INDEX($A$2:$O$15,1,MATCH("Blue",$B$4:$O$4,0))

Sincerely,
Kristopher

 

[Eric W]

I'm not entirely clear on what you're asking, but maybe:

=INDEX(INDIRECT("A2:O15"),1,MATCH("Blue",INDIRECT("A4:O4"),0))

If you need this to change based on row, then maybe

=INDEX(INDIRECT("A2:O2"),MATCH("Blue",INDIRECT("A" & ROW() &":O" & ROW()),0))

 

[KDavidP1987]

Oh my goodness, that worked PERFECTLY! You are a gentleman, scholar, and wizard sir! Thank you!!

 

[Eric W]

You're welcome!