worksheet cell references

The below is an example of a formula that I was shown to get values from 
other worksheets into my current worksheet, using a cell to reference the 
appropriate worksheet needed.  Is there a way to write this so that the 
additional string cell reference is not absolute.  

I would like to copy and paste, but the only reference that changes is the 
A1.  I know that if I put a $ in front of the 1, the A$1 will always 
reference the same cell, when copied and pasted, for the worksheet name I 
need to use, but I need to get different information from sequential cells in 
the worksheets.
=INDIRECT(A1&"!B2")
=INDIRECT(A1&"!B3")
=INDIRECT(A1&"!B4")
=INDIRECT(A1&"!B5")
=INDIRECT(A1&"!B6")

Thanks for yur help.
0
JTSpitz (5)
6/3/2005 6:39:25 PM
excel 39879 articles. 2 followers. Follow

4 Replies
384 Views

Similar Articles

[PageSpeed] 58

    Hi JT,

    I was sent this bit of advice ...

>Enter this into row 2 to get B2, then drag down for 1000 rows.
>
>=INDIRECT($A$1&"!" & ADDRESS(ROW(),2))
>
>HTH,
>Bernie
>MS Excel MVP

    Indeed this worked for me. Another TIP is that the 2 in this formula 
represents the column B
    So if you wanted to get data from Column A, this formula would have to 
read ...

    =INDIRECT($A$1&"!" & ADDRESS(ROW(),1))

    Maybe striaght forward for you MVP's but us newbies like to know :p

    Cheers,

    Wayne


"JT Spitz" <JTSpitz@discussions.microsoft.com> wrote in message 
news:41362D75-3215-4DEE-BD5B-43C3C3E8A0A3@microsoft.com...
> The below is an example of a formula that I was shown to get values from
> other worksheets into my current worksheet, using a cell to reference the
> appropriate worksheet needed.  Is there a way to write this so that the
> additional string cell reference is not absolute.
>
> I would like to copy and paste, but the only reference that changes is the
> A1.  I know that if I put a $ in front of the 1, the A$1 will always
> reference the same cell, when copied and pasted, for the worksheet name I
> need to use, but I need to get different information from sequential cells 
> in
> the worksheets.
> =INDIRECT(A1&"!B2")
> =INDIRECT(A1&"!B3")
> =INDIRECT(A1&"!B4")
> =INDIRECT(A1&"!B5")
> =INDIRECT(A1&"!B6")
>
> Thanks for yur help. 


0
wayne.alex (20)
6/3/2005 7:41:29 PM
If you want B to increase when copied down you can use

=INDIRECT(A2&"!"&CELL("address",B3))

or

=INDIRECT(A1&"!B"&ROWS($B$1:B2))


or

=INDIRECT(A1&"!B"&ROW(2:2))


Regards,

Peo Sjoblom

"JT Spitz" wrote:

> The below is an example of a formula that I was shown to get values from 
> other worksheets into my current worksheet, using a cell to reference the 
> appropriate worksheet needed.  Is there a way to write this so that the 
> additional string cell reference is not absolute.  
> 
> I would like to copy and paste, but the only reference that changes is the 
> A1.  I know that if I put a $ in front of the 1, the A$1 will always 
> reference the same cell, when copied and pasted, for the worksheet name I 
> need to use, but I need to get different information from sequential cells in 
> the worksheets.
> =INDIRECT(A1&"!B2")
> =INDIRECT(A1&"!B3")
> =INDIRECT(A1&"!B4")
> =INDIRECT(A1&"!B5")
> =INDIRECT(A1&"!B6")
> 
> Thanks for yur help.
0
PeoSjoblom (789)
6/3/2005 7:48:01 PM
I am a little confused by the references in your response.  The first one you 
listed is perfectly understandable and, in fact, worked beautifully.

The second one seems to be indicating more than one cell limited to the same 
column.  Can this one be used, for instance, in addition formulas?

The third one seems to be limiting to one column, one cell.

In any case, your response helped solve my problem.  Thank you for your help.

Take care

JT

"Peo Sjoblom" wrote:

> If you want B to increase when copied down you can use
> 
> =INDIRECT(A2&"!"&CELL("address",B3))
> 
> or
> 
> =INDIRECT(A1&"!B"&ROWS($B$1:B2))
> 
> 
> or
> 
> =INDIRECT(A1&"!B"&ROW(2:2))
> 
> 
> Regards,
> 
> Peo Sjoblom
> 
> "JT Spitz" wrote:
> 
> > The below is an example of a formula that I was shown to get values from 
> > other worksheets into my current worksheet, using a cell to reference the 
> > appropriate worksheet needed.  Is there a way to write this so that the 
> > additional string cell reference is not absolute.  
> > 
> > I would like to copy and paste, but the only reference that changes is the 
> > A1.  I know that if I put a $ in front of the 1, the A$1 will always 
> > reference the same cell, when copied and pasted, for the worksheet name I 
> > need to use, but I need to get different information from sequential cells in 
> > the worksheets.
> > =INDIRECT(A1&"!B2")
> > =INDIRECT(A1&"!B3")
> > =INDIRECT(A1&"!B4")
> > =INDIRECT(A1&"!B5")
> > =INDIRECT(A1&"!B6")
> > 
> > Thanks for yur help.
0
JTSpitz (5)
6/3/2005 8:49:21 PM
The first one will change copied down/across, the latter 2 are limited to 
column B
Thanks for the feedback


Regards,

Peo Sjoblom

Re

"JT Spitz" wrote:

> I am a little confused by the references in your response.  The first one you 
> listed is perfectly understandable and, in fact, worked beautifully.
> 
> The second one seems to be indicating more than one cell limited to the same 
> column.  Can this one be used, for instance, in addition formulas?
> 
> The third one seems to be limiting to one column, one cell.
> 
> In any case, your response helped solve my problem.  Thank you for your help.
> 
> Take care
> 
> JT
> 
> "Peo Sjoblom" wrote:
> 
> > If you want B to increase when copied down you can use
> > 
> > =INDIRECT(A2&"!"&CELL("address",B3))
> > 
> > or
> > 
> > =INDIRECT(A1&"!B"&ROWS($B$1:B2))
> > 
> > 
> > or
> > 
> > =INDIRECT(A1&"!B"&ROW(2:2))
> > 
> > 
> > Regards,
> > 
> > Peo Sjoblom
> > 
> > "JT Spitz" wrote:
> > 
> > > The below is an example of a formula that I was shown to get values from 
> > > other worksheets into my current worksheet, using a cell to reference the 
> > > appropriate worksheet needed.  Is there a way to write this so that the 
> > > additional string cell reference is not absolute.  
> > > 
> > > I would like to copy and paste, but the only reference that changes is the 
> > > A1.  I know that if I put a $ in front of the 1, the A$1 will always 
> > > reference the same cell, when copied and pasted, for the worksheet name I 
> > > need to use, but I need to get different information from sequential cells in 
> > > the worksheets.
> > > =INDIRECT(A1&"!B2")
> > > =INDIRECT(A1&"!B3")
> > > =INDIRECT(A1&"!B4")
> > > =INDIRECT(A1&"!B5")
> > > =INDIRECT(A1&"!B6")
> > > 
> > > Thanks for yur help.
0
PeoSjoblom (789)
6/3/2005 9:15:33 PM
Reply:

Similar Artilces:

Excel data in one cell, need to copy specific values to another worksheet in different cells
I am attempting to copy financial data from one worksheet to another. Unfortunately, the worksheet that I am copying from is formatted as one column of data in column A. The data appears as follows in column A: DISTRICT: Adams County/Ohio Valley Local COUNTY: Adams IRN: 061903 ADM, VALUATION & MILLAGE (1983-1984) SOURCE: SF12 Line 3 Basic ADM 4,916 Assessed value 480,468,317 Total ADM 5,769 Valuation/Basic ADM 97,736 Voted millage (Incl JVS) 20.80 Class1 eff tax rate 20.14 BASIC STATE AID (1983-...

drop or paste to a cell problem
Hi, When i copy (or drag) a cell and then paste (or drop) it ,the target cell gets the value only. This happenes even when I specify to paste the formula only... Where did i go wrong ? Thank you, Victor If I copy a range of cells from a filtered list (some hidden rows intermingled), then the formulas convert to values when I paste (or ctrl-drag and drop). Is your range filtered? Eli Fadida wrote: > > Hi, > > When i copy (or drag) a cell and then paste (or drop) it ,the target cell > gets the value only. > This happenes even when I specify to paste the formula only....

How do i create a macro that saves the filename that is equal to a cell in the sheet?
How do i create a macro that saves the filename that is equal to a cell in the sheet? -- Chukka ------------------------------------------------------------------------ Chukka's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29365 View this thread: http://www.excelforum.com/showthread.php?threadid=491092 with activeworkbook .saveas filename:="C:\myfolder\" & .worksheets("sheet99").range("z99").value _ & ".xls", fileformat:=xlworkbooknormal end with No checking at all! Chukka wrote: > > How ...

print a worksheet out from another worksheet
Hi all, I have a validation list that shows all the worksheets in the workbook, I want to beable to select the a worksheet from the list and print it out without having to go to the sheet. Can this be done if so how? Cheers --------- Message sent via www.excelforums.com Hi With the list in A1 on Sheet1 for example you can use this macro Sub printtest() Dim str As String str = Sheets("Sheet1").Range("A1") Sheets(str).PrintOut End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "rutima - ExcelForums.com" <a.caton@ntlworld-dot-com.no-spam.invalid&g...

filtration of worksheets tallying 2 million seperate phone numbers #2
Hi, I am a loan officer at a mortgage brokerage. We have recently put the new nationwide no-call list on an excel spreadsheet. I have 2 questions for you. First: is there a way to make excel have more rows than 65,536 (preferably like 2 million) as the colorado part of the national no-call list is about 2 million. And second;can you tell me how to filter one workshet against a second with 30 columns and 65,536 rows(the filter feature I use only takes into consideration the left-most column) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com...

Merging worksheets #2
I would like to merge the data on two worksheets, can I and how do I do that? -- Ajtorrence Have it all on one sheet? or add it together on a 3rd sheet? Please provide more info. :) ************ Anne Troy www.OfficeArticles.com "Ann" <Ann@discussions.microsoft.com> wrote in message news:81C2F7CF-ED32-4A6C-BE36-1512B18FDA5D@microsoft.com... >I would like to merge the data on two worksheets, can I and how do I do >that? > -- > Ajtorrence ...

Copying Formulea from one cell to another
I want to drag and paste a formulea across a row. But the formulea takes information from another cell, then will miss two columns out, and themn takes information from the next, and so on. I can't seem to find a way of copying the pattern, and I am wasting a lot of time editing each formulea manually. i.e 1st cell should read '=SUM(L15:L10)' 2nd cell next to it should read '=SUM(O15:O10)', the next '=SUM(R15:R10)', and so on. I would like to know a way of doing this automatically. I am using Excel 2003 Standard Edition And would be grate...

how can i get calendar wizard in excel worksheet
i want to now how i can a calendar design in excel worksheet without doing it my self Hi see: http://j-walk.com/ss/excel/files/calarray.exe -- Regards Frank Kabel Frankfurt, Germany "samuel" <samuel@discussions.microsoft.com> schrieb im Newsbeitrag news:BDB21156-C0C1-4E27-8C39-17CB38F26064@microsoft.com... > i want to now how i can a calendar design in excel worksheet without doing > it my self ...

Sorting multiple worksheets simultaneously
Hi!! Is there any way to sort multiple worksheets in a workbook simultaneously (ie instead of sorting each sheet individually)? I have a file with 30+ sheets, each sheet set up identically (ie the same data type in the same column on each sheet), but (obviously) containing different data. There's a complication as well - each sheet has 50 rows, comprising links to other files. Not all of the rows actually contain relevant data (ie in some rows the result of the link is 'zero'), but the rows that contain data come before any rows that do not. For example, on sheet 1 (even th...

sorting grouped cells
Is it possible to sort grouped cells? I have a sheet designed for volleyball stats where total stats show when all the individual game cells are collapsed, and I want to sort the total stats rows and have all the grouped data also be sorted. No - use the data as the source of a pivot table, and then you can automatically sort the resulting calculated values. HTH, Bernie MS Excel MVP "D$" <D$@discussions.microsoft.com> wrote in message news:E97DD9F7-D6C5-44E0-92A4-D4DFCFC62501@microsoft.com... > Is it possible to sort grouped cells? I have a sheet d...

Copy a Worksheet problem
Hi. I am trying to copy a worksheet by using CTRL and drag. When I click on the tab I am getting a 'stop' symbol (circle with a diagonal line through it). I have used this procedure many times before. What has changed? What can I do? Thanks, Dave Moore Perhaps you have protected the workbook under Tools>Protection Gord Dibben MS Excel MVP On Sat, 5 Jul 2008 08:31:20 -0700 (PDT), david.moore@uuplc.co.uk wrote: >Hi. >I am trying to copy a worksheet by using CTRL and drag. >When I click on the tab I am getting a 'stop' symbol (circle with a >diagonal line t...

Autofilling Cells for Daily/Hourly Schedule
I'm trying to create a square footage spreadsheet that gives me an idea of how much material I can process in a week's time. I have a list of jobs on the left side of my spreadsheet and on the right side starting at column 'J' I have hours listed horizontally for example: an 8 hour day from 6-2pm would be labeled 6, 7, 8, 9, 10, 11, 12, 1, 2. I list the sq ft in the 'F' column and then my cells count out by the amount of sq ft until it reaches the total then stops. So, if I do 100 sq ft per hour and I put in cell F2 that my sq ft for a job is 500, then ...

Worksheet in a worksheet?
Is there any way to insert a worksheet in an Excel worksheet, like you can insert a Word document in an Excel worksheet? Regards Philip Have you tried Insert>Object>Create from File - enter the filename and check the Display as icon checkbox "Philip Reece-Heal" wrote: > Is there any way to insert a worksheet in an Excel worksheet, like you can > insert a Word document in an Excel worksheet? > > Regards > Philip > > > I don't think so. You can insert a workbook into a worksheet, though. Just like a .doc file (insert|object). Philip ...

Insert a hyperlink as the text in the cell?
If I have cells A1-A5 as these IP addresses: http://12.23.34.11 http://12.23.34.12 http://12.23.34.13 http://12.23.34.14 http://12.23.34.15 and they are not hyperlinks yet, is there a way to get Excel to insert a hyperlink where the hyperlink will automatically point to the contents of the cell itself ie. the hyperlink in cell A1 should "http://12.23.34.11" and so on? I know you can do this manually, but the automatic option would save a LOT of time. Thanks, Alain In B1 enter: =HYPERLINK(A1,A1) and copy down -- Gary''s Student - gsnu201001 ...

Conditional Formatting on Multiple Worksheets
I want to apply conditional formatting to the same cell on 12 worksheets within one workbook in Excel 2007. As soon as I select multiple worksheets, the conditional formatting option is greyed out. The workbook is not shared or protected and I used to be able to do this in Excel 2003. Please help! Hi, That is how it seems to work in 2007, this could be intentional or it could be a bug, I will file it as a bug report. Your solution: Format the first cell or range on one sheet. Click the Format Painter Click the tab for the first sheet you want formatted the same way, either Shift c...

Worksheet within a Worksheet
Does anyone know if there is a way to have a worksheet within a worksheet? I need to reference an extensive list of account numbers within a worksheet and I don't want the account numbers running all the way down the sheet. What do you mean by "reference"? What exactly are you trying to do? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- <michaelburkett@gmail.com> wrote in message news:1120850412.61...

pivot table: problem with multiple groupings in different worksheets
Hello. I'm having a bit of trouble the group function in a pivot table. Let's say we've got a table with columns "district" and sales (just an example), and I create two different pivot tables with the same origin. In one of the pivot tables I want to group regions in some way, and define different sets in the second pivot table. Here comes the issue: when I right click the selection...-> group, excel defines a new property on the origin data, a new field called "district2". That field also appears on the second pivot table. If I try to group differently the ...

Moving sum from one cell to another cell
Hello, Brand new to this forum and fairly new to Excel. I have created a for for keeping up with loan activity our farm makes to it's employees. Attached find a copy of the form. What I am wondering is whether it i possible to take the "loan balance" (which is the red cell on th right) and transfer that value to the "previous balance" cell (th other red cell to the left and staggered down one row) and have thi operation repeatable each time a loan entry is made. I only want th loan balance number duplicated only once for each seperate entry in th previous balance c...

Protected Worksheet Tab Order
I have a protected worksheet in Excel. After I protected the sheet I selected a range of cells that users could edit. When the sheet is protected the tab order for the range of cells that the user could edit will not work, I can nagivate using the arrows but not by tabbing. Does anyone know how to set the tab order on a protected worksheet? You must select the range of editable cells BEFORE you protect the sheet. Format these cells to "unlocked" then protect the sheet. Tab order will be left to right then down, left to right. If you have some other Tab order you wi...

how to merge data from numerous worksheets into one sheet.
Hi,I'm trying to merge data from numerous worksheets, with varying numbers of rows, but the same column headings, into the one worksheet. i've been cutting and pasting to get the desired results but it takes at least 15 minutes per workbook. Does anyone know if this is feasible? (Skill level?, I would say I'm no more than a casual user) Thanks in advance. What specifically is meant by 'merge data' To do complete rows, filter the book to be copied and Select, Copy and Paste as one selection, To do matching cell data, use VLookup for the columns required. Any further...

saving individual worksheet
When I save in Excel, how do I save individual worksheets separately? Even when I save Worksheet 3, when I open it, it shows up as Worksheet 1. Any clues what I'm doing wrong? Thanks for any help. I'm a newbie (shocker!). bob Bob, This is not typical, so how are you saving the individual worksheet? What steps do you take? -- HTH Bob Phillips "bb" <bb@earthlink.net> wrote in message news:Om%5b.110$Yt.17@newsread4.news.pas.earthlink.net... > When I save in Excel, how do I save individual worksheets separately? Even > when I save Worksheet 3, when I open ...

Is there a keystoke to navigate between worksheets in a workbook?
Ctrl-PageUp and Ctrl-PageDown -- HTH Bob Phillips "Mmarv" <Mmarv@discussions.microsoft.com> wrote in message news:F4193031-FE15-479E-ACD7-09AD409BCEA9@microsoft.com... > ...

Heps to design Locked/Unlocked cells in protected worksheet
Download it from http://www.addintools.com The Cell Lens Of Locked&Unlocked render and change the background color of cells according to their lock/unlock state: the locked cells to gray, the unlocked cells to blue. Assist has also offered a Quick Lock button to lock the selected range at once and a Quick Unlock button to unlock immediately. Now you are very clear about which cells are locked and which cells are unlocked. Just cancel this Cell Lens, the background color will be restored. Cell Lens Of Data Type render and change the background color of cells according to their data ...

Sorting one worksheet from another worksheet
I am wanting to sort data in a worksheet from a command button on a different worksheet. I want the focus to stay (or at least return) to the worksheet containing the command button. Is this even possible? If so, could someone supply a sample of code? Both worksheets are in the same workbook. Using excel 2003 . Yes. The trick is to not select the other sheet. Option Explicit Private Sub CommandButton1_Click() Dim wks As Worksheet Dim myRng As Range Set wks = Me.Parent.Worksheets("Sheet2") 'some other sheet With wks Set my...

Embedding worksheet into Word
Has anyone had the experience where when they embed a worksheet into Microsoft Word, a portion of the worksheet gets cut off? Is there an easy fix to this? ...