Using a variable for which sheet or range top copy from.

[manny13]

Here's my problem....

I want to compare two teams when they play each other. I'd like to just put the name of the team and have it populate a new tab from the team range elsewhere in the spreadsheet.

So I have a range name of TEAMa. If I what c2 cell from that range I would put =TEAMa.c2

Here is my problem, I'd like a variable for TEAMa in the formula. I don't know how to do that ....so I created a concatenate to create =TEAMa.c2 (this way I could change the team name) but when I copy to a new cell as a text, it show as a text. I have to hit F2, hit HOME and ENTER and it changes to a formula and gets the data from the cell. I tried to recalc, but it doesn't work. Since I have about 100 cells. It would take forever to go to each one.

To summarize...I either need to put a variable for TEAMa in this formula =TEAMa.c2 or a way I can copy from a =concatenate so the text it creates copies as a formula not text.

Thank You

[ken johnson]

Say A1 is the cell with the CONCATENATE function and its return value is "TEAMa.C2", then =INDIRECT(A1) in any other cell will return the value in TEAMa.C2.

You could also skip the CONCATENATE step. Say A1 holds "TEAMa" and A2 holds "C2" then =INDIRECT(A1&"."&A2) will do the same.

Ken Johnson

[manny13]

Thank You