Refer to sheet name specified in other cell

Referring to cells in other sheets requires sythax: 
'sheetname'!cellname.  I have the situation in which sheet name is 
specified in another cell.  How can I refer to cell in that sheet?

E.g.
A1 = "sheet1"

A2 = "'A1'!B3" (I would like to refer to cell B3 in sheet1)

Is that possible to do?

Marko
0
marko1 (21)
3/4/2005 8:50:14 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
442 Views

Similar Articles

[PageSpeed] 54

"Marko Pinteric" <marko@pinteric.com> wrote in message
news:ajVVd.9824$F6.1952421@news.siol.net...
>
> Referring to cells in other sheets requires sythax:
> 'sheetname'!cellname.  I have the situation in which sheet name is
> specified in another cell.  How can I refer to cell in that sheet?
> E.g.
> A1 = "sheet1"
> A2 = "'A1'!B3" (I would like to refer to cell B3 in sheet1)

You can use INDIRECT for this, ie:

=INDIRECT(A1&"!B3")

or some variation of same.

Rgds,
Andy


0
andy.j.brown (443)
3/4/2005 9:07:16 AM
With A1 containing: Sheet1

Put in A2: =INDIRECT("'"&A1&"'!B3")

A2 will return the same as: =Sheet1!B3
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Marko Pinteric" <marko@pinteric.com> wrote in message
news:ajVVd.9824$F6.1952421@news.siol.net...
>
> Referring to cells in other sheets requires sythax:
> 'sheetname'!cellname.  I have the situation in which sheet name is
> specified in another cell.  How can I refer to cell in that sheet?
>
> E.g.
> A1 = "sheet1"
>
> A2 = "'A1'!B3" (I would like to refer to cell B3 in sheet1)
>
> Is that possible to do?
>
> Marko


0
demechanik (4694)
3/4/2005 9:13:27 AM
Reply:

Similar Artilces:

Delete all sheets in workbook that contain "Dump" in the name
Hello, I have a workbook that generally contains 15-18 worksheets. Is it possible to automate the deletion of only the worksheets with a name that contains the word "Dump". The amount of worksheets that contain the word "Dump" ranges from 3-7. Thanks, Patrick -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201005/1 You could use a macro: Option Explicit Sub testme() Dim sh As Object 'could be any kind of sheet Dim HowManyDeleted As Long HowManyDeleted = 0 For Each sh In Ac...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

moving to next cell using "Enter"
With Excel XP (Windows EX also) Excel seems to have a problem remembering what direction to move the focus when the Enter key is hit. Is there a fix or a way of keeping it the same? Thanks -- Joseph Meehan Dia duit Hi, Go to Tools->Options and choose Edit tab. In that choose the direction of the Enter key to the way you want. Govind. Joseph Meehan wrote: > With Excel XP (Windows EX also) Excel seems to have a problem > remembering what direction to move the focus when the Enter key is hit. Is > there a fix or a way of keeping it the same? > >...

doc w/ same name open?
When I try to open a document i get an error message a document with the same name is already open. Cannot open 2 docs w/ same name?? Hi Jeff this is a Excel restriction. Excel can only open one file with the same name (though they are in different directories) -- Regards Frank Kabel Frankfurt, Germany "Jeff C." <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:15ee401c41e38$58f639b0$a001280a@phx.gbl... > When I try to open a document i get an error message a > document with the same name is already open. Cannot open > 2 docs w/ same name?? I don...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

How to move data from one sheet to another automatically
I want the 3 cells that i have containing (cell one) Name, (Cell 2) Street, and (Cell 3) City, state, and zip to go from my sheet one to another sheet auto matically using a formula. (not macros or copying) =Sheet1!A1 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Harleyrider888" <Harleyrider888@discussions.microsoft.com> wrote in message news:93E6891B-9445-434B-AAB4-3AA9E2A85DD9@microsoft.com... > I want the 3 cells that i have containing (cell one) Name, (Cell 2) > Street, > and (Cell 3) City, state, and zip t...

Data Validation in cell
Hi I have a sales order form with the equipment on the second sheet as "PartNo" "Description" "Cost" selected each column of data then clicked in the cellname box and called them "PartNo" "Description" "Cost" On the sales order form I have the columns "Qty" PartNo" "Description" "Serial No" "QtyDes" "Price" Total" I have set up data validation for the description as a list with =Description what I want it to do is display the part no in the part number Column and the cost in...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

How can I make a Chart data series treat blanks as "Empty" cells
My Data Series contains blanks as the result of formula calculations. The location and number of blanks can change with each recalculation as input criteria change. I can make my Chart ignore an "Empty" cell easy enough but my cells still contain the formula which created the blank ("") cell and therefore, not truely empty. I want to plot the series, about 7000 values, without the blanks showing up as zeros and ruining my trendline fit. Removing those rows is not an option as I have to retain the x-axis value as place holder on the chart. Thanks for your help; ...

Finding emply sheets
Excel 2007 How can you quickly identify completely empty sheets in a workbook? I get workbooks from people that have lots of emply sheets, and need to quickly identify and delete these sheets? Thanks, Will Manually? I select the worksheet and hit ctrl-end to see where the last used cell is. If I'm taken to A1, then I check to see if that cell is used. If A1 is not empty, then the worksheet is used. If A1 is empty, then there's a good chance that the sheet isn't used and can be deleted. But this isn't a foolproof method. The sheet could be used for other purposes...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

Newbie question
Hi, Is there a way to flag selected cell ranges to always stay together when they print? In Word they have a "keep lines together" option -- is there anything comparable in Excel to ensure that selected cells/rows stay together on a page even when new information is added (not only within the selected range but also when additional cell ranges are added)? I need to ensure that the cell ranges aren't split by page breaks. I currently use print preview to see where they are breaking once I add new information and then move the page breaks before printing. Since new...

Function to get worksheet name
Its seems pretty simple. 1. which functions returns parameters like worksheet name ? Same way can i get what is file name ? Thanks in advance.. ssuryarao@gmail.com wrote... >Its seems pretty simple. > >1. which functions returns parameters like worksheet name ? > >Same way can i get what is file name ? The only built-in function that returns these is CELL. You could use CELL("Filename",A1) to get both the workbook filename and the worksheet name corresponding to the range reference 2nd argument *IF* you've already saved the file. In that case, it'll retu...

Date Formats for Cells Do not match System Date Formats
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just loaded Office 2008 and then update to the latest update 12.2.4 <br> I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in &quot;Numbers&quot; the dd/mm/yyyy format is used so &quot;Numbers&quot; is picking up the system preference why not the excel. Did you by any chance have ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Creating Summary Report on Seperate Sheet
Im hoping this really isn't as complicated as I've found it to be, but it's 4am and I don't seem to be getting very close. I know 0 VBS stuff, so that may be a quick answer for me to learn a little, but here is the general idea. I have a spreadsheet with two sheets, the first is a checklist, where you can enter numbers that correspond to the items that apply to you. At which time formulas derive other information. For example, column A is how much, on a scale of 1-10 the person likes pie, and then a formula in column C decides how much that will cost the person. So a user c...

Nested IF that will ignore blank (empty) cells
Hi, I have a simple nested IF function (Excel 2003) which compares numbers in two cells, then returns a word depending on whether one number is greater than, equal to, or less than the other: =IF(F3>G3,"Yes",IF(F3=G3,"Equal",IF(F3<G3,"No"))) Either (or both) of the compared cells might contain the number 0 (zero) in which case I want the IF function to display the appropriate word. However if the compared cells are blank (have no data in them), my unsophisticated IF function thinks that F3=G3 and returns the word "Equal". How can I get the ...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

entering a date into cell and get ## symbols
i am entering a date value into a cell and i that it shows is "##" Even when i print it shown "##" and no date why? Make the cell wider -- Regards, Peo Sjoblom Portland, Oregon "Steve" <Steve@discussions.microsoft.com> wrote in message news:2D47F6AA-9DA5-4CCA-88D4-171AF660CE91@microsoft.com... >i am entering a date value into a cell and i that it shows is "##" Even >when > i print it shown "##" and no date why? Steve wrote: > i am entering a date value into a cell and i that it shows is "##" Even when ...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

Help with #DIV/O! ...need cell value to come up with answer
When dividing a number by zero (0) the following error appears #DIV/O! however instead of this I would like the number 0 to appear. How can this be done? I have tried conditional formatting..but cant get it to work..any suggestions. Thanks Tim Harding =if(a2=0,0,b2/a2) is the way On 28 Aug 2003 01:47:44 -0700, tim.harding@royalmail.com (Tim Harding Royal Mail) wrote: >When dividing a number by zero (0) > >the following error appears #DIV/O! however instead of this I would >like the number 0 to appear. > >How can this be done? > >I have tried conditional format...