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.
    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!