dynamically referencing to another worksheet

Hi all,

I'm relatively new to Excel but I'm working on a spreadsheet for work.
Let's say I have a workbook with a separate tab (worksheet) called
"exam1".  On the first worksheet I can reference to a cell (let's say
A2) in the other by using "=exam1!A2".

Here's what I need to do though.  On the first worksheet in column A, I
will have a list of all the tabs that will eventually be included in
the spreadsheet (e.x. A1 = exam1, A2 = exam2, A3 = exam3, etc.).  Is
there a way I can modify the code from the first worksheet above
(=exam1!A2) to be dynamic?  Something like "=A1!A2" is what I had in
mind, but that syntax doesn't work.  Anyone have any ideas?

Thanks in advance.

0
6/23/2006 1:10:21 PM
excel 39879 articles. 2 followers. Follow

2 Replies
474 Views

Similar Articles

[PageSpeed] 4

Have a look in the help index for INDIRECT

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"D23" <Destroyer23@gmail.com> wrote in message 
news:1151068220.955903.208590@r2g2000cwb.googlegroups.com...
> Hi all,
>
> I'm relatively new to Excel but I'm working on a spreadsheet for work.
> Let's say I have a workbook with a separate tab (worksheet) called
> "exam1".  On the first worksheet I can reference to a cell (let's say
> A2) in the other by using "=exam1!A2".
>
> Here's what I need to do though.  On the first worksheet in column A, I
> will have a list of all the tabs that will eventually be included in
> the spreadsheet (e.x. A1 = exam1, A2 = exam2, A3 = exam3, etc.).  Is
> there a way I can modify the code from the first worksheet above
> (=exam1!A2) to be dynamic?  Something like "=A1!A2" is what I had in
> mind, but that syntax doesn't work.  Anyone have any ideas?
>
> Thanks in advance.
> 


0
dguillett1 (2487)
6/23/2006 1:18:47 PM
Try this:

=INDIRECT("'"&A1&"'!A2")

and copy down as needed.
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"D23" <Destroyer23@gmail.com> wrote in message
news:1151068220.955903.208590@r2g2000cwb.googlegroups.com...
Hi all,

I'm relatively new to Excel but I'm working on a spreadsheet for work.
Let's say I have a workbook with a separate tab (worksheet) called
"exam1".  On the first worksheet I can reference to a cell (let's say
A2) in the other by using "=exam1!A2".

Here's what I need to do though.  On the first worksheet in column A, I
will have a list of all the tabs that will eventually be included in
the spreadsheet (e.x. A1 = exam1, A2 = exam2, A3 = exam3, etc.).  Is
there a way I can modify the code from the first worksheet above
(=exam1!A2) to be dynamic?  Something like "=A1!A2" is what I had in
mind, but that syntax doesn't work.  Anyone have any ideas?

Thanks in advance.


0
ragdyer1 (4060)
6/23/2006 3:03:42 PM
Reply:

Similar Artilces:

Copy a currency value from one control to another
I have a text box (called ExtendedCost), that has a sum statement in it. I want to copy the value on the exit control event to another text box control (called WithdrawlAmount) on the same form and write the value into WithdrawlAmount box on the Enter Event for the control box, so people don't have to input the same number into the control box. Is there an easy way to do this? If it's the same calculated value, just use the same expression. Generally, don't store calculated values. Just perform the calculation as needed. If you need more help with this, describ...

Further Info on changing text in one cell to to criteria of another.
I have a workbook that charts the progress of my clients. One of m cells automatically determines their age, and when they become olde than six, I need another cell's text to automatically change to "AO (standing for "Aged Out" of my program). I cannot us the functio =IF(A1>6,"AO"," ") because I already have the clients status imputed i that cell. I need it to be a type of conditional formatting or VB cod so that it will automatically change once the reach 6. I have attache an example of my workbook. It has a VB code that changes the row colo based on ...

Trace precedents from another worksheet
I'm trying to trace precedents from another worksheets. However, when I do this, I just get an arrow leading to a worksheet icon. Clicking on the icon just gives me a formula, and worksheet that contains the precedents shows no arrows or anything. I remember that this used to work fine in earlier versons of Excel. (I have Excel 2002). Do you know what's going wrong? Thanks, Katie -- ModelerGirl ------------------------------------------------------------------------ ModelerGirl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6364 View this thre...

Dynamics and Outlook
I just installed Microsoft Dynamics GP 10.0 and now my Outlook can't open. Can anyone tell me how to fix? It seems your outlook is already open in background, open Task Manager and kill the "Outlook" process, then try to open outlook again. Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 mohdaoud@gmail.com http://mohdaoud.blogspot.com/ "Joann" wrote: > I just installed Microsoft Dynamics GP 10.0 and now my Outlook can't open. > Can anyone tell me how to fix? ...

How do I associate task list with another calendar?
I have an extensive task list that shows in one calendar view. I would like it to show in another. Can I associate it, or does it have to all be reentered? "holyharvester" <holyharvester@discussions.microsoft.com> wrote in message news:1A0249B2-3A52-43ED-9509-E65851724BD9@microsoft.com... >I have an extensive task list that shows in one calendar view. I would like > it to show in another. Can I associate it, or does it have to all be > reentered? You first: state your Outlook version. -- Brian Tillman [MVP-Outlook] Only the default task list dis...

Formula copying from one workbook to another...
Hello Excel community friends. Here's my problem: I trying to copy a formula from a worksheet in one workbook to a worksheet (of the same name and same cell location) in another workbook. Say the formula from workbook1/worksheet1 is =sum(1+1) and the name of workbook1 is "Me" and the name of the worksheet1 is "Sheet1" and located in cell A1. Of course, when you copy the formula from "Me" to another workbook (named, say, "You") and to the same location (A1) and on the same named worksheet, "Sheet1", the preface of the formula is s...

set a value to another cell
how to set(not get) a cell value to another cell? is it possible? A cell formula can get, but only a macro can set. Some exceptions (manually): Scenarios, Goal Seek, Subtotals, Text to Columns and Advanced Filter, none of which is applicable to your situation. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "news.microsoft.com" <whoknows@microsft.com> wrote in message news:%23kjDDZRoIHA.264@TK2MSFTNGP05.phx.gbl... > how to set(not get) a cell value to another cell? > is it pos...

Copying records from one (sub)form to another subform using Append
I have a mainform with products (and thier details) from a products table and a subform showing ingredients (from an ingredients table). I wish to select existing ingredient records from one subform and copy them to so that they appear as ingredients in a new Mainform. I have a selection box next to each of the ingredients to enable selection but how do I "copy" these selections to the new record on the new mainform. Do I use an append query to do this? Any guidance on doing this would be great (not too experienced in this area). Many Thanks On Tue, 9 Feb 2010...

Another MDI or SDI question
I would like to convert my VB app to VC++, but I am unsure as to which framework to use. The application will interface to a hardware device to monitor, record and analyze data. There will only be one set of data, but I would like to view it in various formats (spreadsheet, graph, etc.). Because of the multiple views, am I correct to assume I should choose MDI? Any advice would be appreciated. This sounds definitely like MDI. I have built, in all my apps, two SDI apps, and regretted both of them (I had to rewrite them as MDI). The rest of my apps are dialog apps, and one of those uses tabbed ...

Error message with copying sheet to another workbook
I always encounter an error message when i copy a sheet from one workbook to another workbook. The error message is as below: ================ A formula or sheet you want to move or copy contains the name '____123graph_A', which already exists on the destination worksheet. Do you want to use this version of the name? - to use the name as defined in the destination sheet, click Yes - to rename the range referred to the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box. ================ I have tried to click No and enter a new Name, but afterward...

Excel---Copying text from one cell a a sheet to another cell on a
I know this will sound stupid but, I cannot get TEXT that I type in one cell on sheet 1 to show up in a cell on sheet 15. Aby help?? If the cell on the 1st sheet is A1, in the cell on Sheet15, you put the formula =Sheet1!A1 On Mon, 18 Oct 2004 19:33:01 -0700, "Reeezzzy" <Reeezzzy@discussions.microsoft.com> wrote: >I know this will sound stupid but, I cannot get TEXT that I type in one cell >on sheet 1 to show up in a cell on sheet 15. Aby help?? ...

Adding custom fields to dynamically created controls.
I created a custom Outlook (2003) form that imports data from Access to populate user defined fields and create additional controls based on the number of records in an Access query. Everything works fine except the data the user enters for the dynamically created controls don't hold their value when the form is sent. I am quessing that is because there is no user defined fields bound to them. (how do I do this) I have been looking on the net but cannot find anything close enough. Any help (examples) would be appreciated. Look at http://www.outlookcode.com/d/formcontrols.htm, lots of inf...

A macro that copies entire worksheet to another
Hi- Can anybody help me with a macro that copies an entire worksheet to another (or creates a new worksheet named by a value in a cell)? I plan to use a push button to copy this data. I'd appreciate any help - Jim Ayers Hi Jim, Why don't you revert the easiest and the most illustrative method of writing a macro (and learning how to write a macro) by using the macro recording facility? There may be slight changes depending on your Excel version, but generally you click Tools --> Macro --> Record New Macro, then mimic exactly what you want to happen, stopping recording a...

SBS as a DC but CRM on another Server
Hi everyone, I just have few questions which I hope someone out there will be able to answer for me. We have an SBS 2003 Server which is our DC and we are running SQL Server, Exchange Server and Microsoft CRM on a non-raided server. 1. Is it possible to have an SBS Server 2003/8 running as a DC with SQL Server & Exchange on it but run Microsoft CRM on another server running Windows 2003/2008 Server. We just want to spread the workload on the DC. 2. Bar the obvious reasons for using raid, would you recommend having SBS running of a raided machine? What about CRM if it can be run from an...

viewing another users calendar
Hi All, When attempting to view one certain users calendar the message "unable to display the folder. the calendar folder could not be found" is displayed, this is also the same for the inbox etc. This is only happening for one user. All other users can view this calendar and i can view all other users calendars. Does any one have any suggestions? ...

Set control AfterUpdate event to procedure in another module
Hi, i know i can set a controls AfterUpdate event to a user defined procedure if it is in the same module as the code, however is it possible to set it to a procedure in a different module? i.e. By setting in the OnFormLoad() event: cbo.AfterUpdate = "=Module1.Translate()" I cant get it to work, it just says "the expression you entered has a function name that microsoft office access cannot find". The Translate procedure is public. Any thoughts much appreciated. Thanks in advance, Chris If Module1 is a standard module (not a class module, and not the module of a form...

Dynamic Date Ranges
One one sheet I have a time series of data sorted by month. How do I go about (on a different sheet) using some sort of dropdown box (either through data validation or a combo box), so that the user can select a beginning date and end date, and the spreadsheet will display only the information in that time period. The number of columns would have to dynamically adjust I believe. Thanks! -- jc94321 ------------------------------------------------------------------------ jc94321's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27258 View this thread: http://ww...

automating data copy from dynamic invoice to single spreadsheet
Hi - I am a newbie vis-a-vis VBA. I am trying to copy data off of an invoice (that keeps changing every day) to a single spreadsheet. So far, I have used the following code: Sub CopyCells() Dim rngData As Range, rngDest As Range Dim i, j As Integer Set rngData = Sheets("Invoice").Range("A16:g35") Set rngDest = Sheets("InvData").Range("A1").End(xlDown).Offset(1, 0) For i = 1 To rngData.Rows.Count If rngData.Cells(i, 1) <> "" Then rngDest.Offset(j, 0).Value = Sheets("Invoice")....

Want to auto name worksheets
I work with a large spreadsheet that has several worksheets in it. Is it possible to create a macro that can copy the contents of a cell and then paste that as the new name for the worksheet? Reply to doc_cowher@yahoo.com Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Doc, this will do what you want, Sub Name_Sheet() ActiveSheet.Name = [A1] End Sub -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any re...

In Excel 2000, How do you select the whole of a worksheet (Select.
In Excel 2000, How do you select a whole worksheet (Select All) with ONE click of the mouse? Cheers!! click on that little grey button to the right of the column headers and at the top of the row headers. Using the keyboard (you didn't ask!). Ctrl-a (twice in xl2003.) Rascal wrote: > > In Excel 2000, How do you select a whole worksheet (Select All) with ONE > click of the mouse? Cheers!! -- Dave Peterson ...

Workflow : Can't see fields in dynamic values
Hi everybody, I have two entities , one of them is having picklist values. I want from workflow to update the another entity ( a particular field) , when i chose a certain value in the picklist . I can not see all the fields in Dynamic values. I am unable to see the fields of Owner and Account (these are lookup fields). If Account and Roles. Job function = Account Manager then update Account. Account manager= Account and Role. Owner ( i can not find Owner in dynamic fields) any suggestion ? ...

Creating a graphic from worksheet's data: like a "progress" thermo
Is it possible to create graphics from data -- other than pivot tables/charts? Specifically, could I somehow create, from data that displays planned and completed dates for a project, a thermometer or some thing even better that shows completion vs. planned...? There's more data than just that, but that's an example. Thanks Andy Pope has instructions for using a graphic: http://www.andypope.info/charts/dollarsplit.htm and for thermometer charts: http://www.andypope.info/charts/thermometer.htm Nicole L. wrote: > Is it possible to create graphics from data -- other than...

INDIRECT doesn't retain data from external worksheets
I'm using the INDIRECT function to bring in data from other spreadsheets. Using this allows me to just populate a cell and automatically point to a new spreadsheet (I have dozens of spreadsheets that hold similar data, and dozens of formulas that pull that data over, so I don't want to manually edit all the formulas). However, it appears that when I use this, the file that it points to needs to be open. I have a fully qualified path ("C:\Documents and Settings\......"), so I don't understand why the document needs to be open. If I don't use INDIRECT, ...

Any way to capture text in a textbox inside another application?
I need to write an application and need to find a way to "spy" on a textbox in another application. It will work the similar to the spy++ where you indicate that you want to spy on a control in another app, and the program will let you pick the control to spy on. From that point on, all the messages sent to the control will be routed to this program. I am wondering how spy++ does that and is there any sample in MSDN shows how to do that? Thanks Andy Andy, > I need to write an application and need to find a way to "spy" on a textbox > in another application. &...

Accept/decline meetings or another user's behalf
Hi Is there a way to give a another user permission to accept/deny meetings? Thanks Regards This probably belongs to the Outlook Group Since this is a SBS Server Group (Assuming you are talking about Outlook) http://social.technet.microsoft.com/Forums/en-US/outlook/threads However You can give another person permission to view and add appointments to your calendar using the steps that are listed below: 1.Within Outlook, click on the Tools menu and select Options. The Options dialog box will appear. 2.Select the Delegates tab. 3.Click the Add button. The Add ...