# Use Cell Data in Formula

• Hey there board,
I am trying to write a formula that, though fairly simple, needs to change as the value of another cell changes. This is for a fantasy football tracker. Basically I need something like this: If(cellA2=Team1, then This Cell = SheetTeam1 cellQ135) and so on for 10 teams. To restate I need this formula to look at a cell on the current sheet, decide what team name is in that cell, and then return a specific cell value from the Sheet with the same name as the Team found. I tried to use nested if statements and it seems that I can only nest an amount two short of the number i need.
=IF(W1="A1",('A1'!Q153),IF(W1="A2",('A2'!Q153),IF(W1="A3",('A3'!Q153),IF(W1="A4",('A4'!Q153),IF(W1="A5",('A5'!Q153),IF(W1="N1",('N1'!Q153),IF(W1="N2",('N2'!Q153),IF(W1="N3",('N3'!Q153),0))))))))
I need this formula to continue as is through N4, and N5 variations.
Thanks for the help all!

• Re: Can formula change when another cell changes?

I would use the INDIRECT funtion

This allows you to create a cell ref with a formula and use it as a range.

CellA2 : "Team1"
ThisCell : =INDIRECT(CellA2&"!Q153")

--
I'm not getting you down am I?

• Re: Can formula change when another cell changes?

Thank alot Radiohead! I was having problem with that indirect method, but you've solved that for me...Much appreciated!!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!