 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006How to reference another cell's content directly in a formula
Hi all, For a cell such as that in A2 below: A B 1 4Q 2005 1Q 2006 2 ='4Q 2005'!$R$7 3 The '4Q 2005' refers to a sheet in the same excel work book with that name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'. Is there any function I can use so that cell A2 uses the data in cell A1 to figure out which sheet name to look for? I'm doing this so that I can copy and paste cell A2 into cell B2 and don't have to manually input '1Q 2006'. Something to the effect of the pseudocode = 'content_of(A1)'!$R$7. When copied and pasted to cell B2, the pseudocode would read = 'content_of(B1)'!$R$7. Thanks. Lokk at INDIRECT in Help HTH -- AP > Hi all, > [quoted text clipped - 17 lines] > > Thanks. This is not the way I want it to work since it requires the entire reference to be in the cell A1. I need it so that it extracts the string contained in cell A1, which is "2Q 2006" and place it in the formula in cell A2 rather than automatically jump into the address contained in cell A1. The appropriate function would be: ='indirect(A1)'!$R$7 in cell A2 However, the above function does not work so I'm trying to find a way to tweak it so that it will work. > Lokk at INDIRECT in Help > [quoted text clipped - 23 lines] > > > > Thanks. The proper syntax is: =INDIRECT("'"&A1&"'!$R$7")  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- This is not the way I want it to work since it requires the entire reference to be in the cell A1. I need it so that it extracts the string contained in cell A1, which is "2Q 2006" and place it in the formula in cell A2 rather than automatically jump into the address contained in cell A1. The appropriate function would be: ='indirect(A1)'!$R$7 in cell A2 However, the above function does not work so I'm trying to find a way to tweak it so that it will work. Ardus Petus wrote: > Lokk at INDIRECT in Help > [quoted text clipped - 25 lines] > > > > Thanks. Actually, since the cell reference is literally 'text', you can forget about the absolutes: =INDIRECT("'"&A1&"'!R7") However, if you need to actually increment *both* the sheet *and* the cell, you could try this: =INDEX(INDIRECT("'"&A1&"'!R:R"),ROWS($1:7)) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > The proper syntax is: > [quoted text clipped - 41 lines] >> > >> > Thanks. =INDIRECT("'"&a1&"'!$r$7") HTH -- AP This is not the way I want it to work since it requires the entire reference to be in the cell A1. I need it so that it extracts the string contained in cell A1, which is "2Q 2006" and place it in the formula in cell A2 rather than automatically jump into the address contained in cell A1. The appropriate function would be: ='indirect(A1)'!$R$7 in cell A2 However, the above function does not work so I'm trying to find a way to tweak it so that it will work. Ardus Petus wrote: > Lokk at INDIRECT in Help > [quoted text clipped - 25 lines] > > > > Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.