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.


    from your example:


    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!