OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

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

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
manny13
Newbie
Newbie


Joined: 04 Sep 2007
Posts: 4
Location: Chicago

PostPosted: Sun Aug 30, 2009 9:04 am    Post subject: Using a variable for which sheet or range top copy from. Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Aug 30, 2009 1:32 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
manny13
Newbie
Newbie


Joined: 04 Sep 2007
Posts: 4
Location: Chicago

PostPosted: Sun Aug 30, 2009 2:02 pm    Post subject: WORKED Reply with quote

Thank You
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group