Repost: Copy and Offset cell reference

Hi Groupies:

I posted this a couple of days ago, but I missed the functions group, no 
reply yet.

I was asked if this was possible and I can not think of how, hopefully,
somebody can help me.

On Sheet 2, I have the following data:

cell B2 =sheet1$N$14
cell B3 =sheet1$N$32

On sheet 3, I need to have

cell B2 =sheet1$N$15
cell B3 =sheet1$N$33

On sheet 4, I need to have

cell B2 =sheet1$N$16
cell B3 =sheet1$N$34

Basically, each sheet is a different month and the references need to
be offset each time. This needs to be done a number of times in this
and other workbooks.

Is there an efficient way to do this?

-- 
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups! 

0
9/5/2008 2:24:45 PM
excel 39879 articles. 2 followers. Follow

1 Replies
473 Views

Similar Articles

[PageSpeed] 16

Sub formulaforshts()
'cell B2 =sheet1$N$14

For i = 1 To 3
Sheets(i).Range("b2").Formula = "=sheet1!n" & i + 13
Sheets(i).Range("b3").Formula = "=sheet1!n" & i + 31
Next i
End Sub


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"CJ" <private@newsgroups.com> wrote in message 
news:D0F12041-9A4F-4693-9C04-8CCE65C9D2FF@microsoft.com...
> Hi Groupies:
>
> I posted this a couple of days ago, but I missed the functions group, no 
> reply yet.
>
> I was asked if this was possible and I can not think of how, hopefully,
> somebody can help me.
>
> On Sheet 2, I have the following data:
>
> cell B2 =sheet1$N$14
> cell B3 =sheet1$N$32
>
> On sheet 3, I need to have
>
> cell B2 =sheet1$N$15
> cell B3 =sheet1$N$33
>
> On sheet 4, I need to have
>
> cell B2 =sheet1$N$16
> cell B3 =sheet1$N$34
>
> Basically, each sheet is a different month and the references need to
> be offset each time. This needs to be done a number of times in this
> and other workbooks.
>
> Is there an efficient way to do this?
>
> -- 
> Thanks for taking the time!
>
> CJ
> ---------------------------------------------------------
> Know thyself, know thy limits....know thy newsgroups! 

0
dguillett1 (2487)
9/5/2008 2:38:48 PM
Reply:

Similar Artilces:

Copying Conditional Formatting Down A Column
Hi all, I'm trying to copy some conditional formatting down a column. Problem is, I want it to be specific to the row it's on. However, at the moment, all it does is copy the same row reference all the way down the column. Is there a means to rectify this - as if I was dragging a formula down the column? Thanks in advance, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=477142 ...

cascade copy?
Is there such a thing as a cascade copy? I know there's cascade delete where if you delete the main record, all related subrecords are deleted but what if I want to do the reverse. For example I have a quote that has subpackages related to it. If I want to copy the main quote, I want all related subrecords copied along. Or is this a programming thing? Thanks, Alan "Alan" <Alan@discussions.microsoft.com> wrote in message news:7A7551D8-B482-4911-9452-3FE801FED2D4@microsoft.com... > Is there such a thing as a cascade copy? I know there's cascade delete where ...

Calculate the function in a cell that is created with CONCATENATE
Background .... $G$1 = a list $L$6 = =CONCATENATE(RIGHT(E1,2)," Q") $M$6 = A7:K78 $N$6 = =CONCATENATE("'",L6,"'",M6) I built a formula with the concatenate function: =CONCATENATE("=VLOOKUP(G1,",N6,",2,FALSE)") That produces the desired formula: =VLOOKUP(G1,'AG Q'!A7:K78,2,FALSE) But, that output function does not calculate unless I go into the cell and F2 F9. Is there a way to have Excel perform that function automatically? -- Matt N16 contains a string that looks like a formula. Consider this very ...

copy level 2 data in an outline
How can I cleanly copy/paste five contiguous columns (B2- F431) of just subtotaled data; I want just the level 2 detail in this range? The worksheet dimensions are A1-N432, with 134 subtotaled rows. All rows are subject to one outline with 3 levels of grouping: 1=grand total, 2=subtotal, 3=all detail. The summary description under grand total and subtotal rows includes as its last word " ... 'Total'" in column B. Thanks for your help! Randy What version of Excel are you using? In XP and 2003 (I don't have 2000 to try it on), you can do the following (simple if c...

Conditional Formatting for Changes to cells
I want to use either conditional formatting or change tracking to change the font color on any changes made to a spreadsheet by a user. Just one user will be using each spreadsheet but the changes are then submitted to someone for data entry into the big iron. Thanks dick Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.Color = vbRed End Sub Bob Umlas Excel MVP "Dick Stapleton" <dick.stapleton@coldist.com> wrote in message news:uxK7%23IqvJHA.4928@TK2MSFTNGP03.phx.gbl... >I want to u...

repost; Automatically updating formulae in multiple sheets
I list sales of 20+ products each week for the year A2-A23 are the products and B-BA are the colums for each week I have multiple sheets that have the same sales results in the same format foreach previous year 2004;2003;2002;2001;2000 Each week I want to compare total sales in the year to date i.e weeks 1-20 with weeks 1-20 in each previous year. so next week it will be 1-21. I have on each sheet a cumulative total for each of the 20 products in the year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for each product. This means each week I have to alter the formula in C...

Copy Picture problems
I've written a macro which copies a selected range in Excel 2000 and and paste it as picture in Power Point. CopyPicture is used and the macro works OK apart from that all columns are not always included in the pasted picture. Grouped columns and selection of visible cells only is used in Excel. e.g from Range A1:Z15 12 visble columns are selected. A different number of columns can be included in the pasted picture when different users runs the macro. Thanks in advance for any help. Mats "MatsN" <MatsN@discussions.microsoft.com> wrote in message news:2A8F...

Text boxes when mouse goes over the cell.
In a document i have recieved from a client, in excell, when i put my mouse over certain color coded cells, text boxes come out with lengthy descriptions. This sems highly usefull, and i would like to know how to do this. Anyone know? It seems all cells with a colored corner have these descriptions. Select the cell of interest, then: <Insert> <Comment> -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Karl" <Karl@discussions.microsoft.c...

Is it possible to count the no. of characters in a cell in Excel?
In order to create Tweets in Excel, I wanted to know if you could count the characters/spaces in a cell. Hi, To count characters excluding spaces =LEN(SUBSTITUTE(A1," ","")) to count spaces =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "karbrown12" wrote: > In order to create Tweets in Excel, I wanted to know if you could count the > characters/spaces in...

Repost
I sent his message earlier but have not been able to view it from here so I apologize if it has already been answered. I had heard that the refresh was released sometime last week but have received no notification from MS about it. When will I know? Will MS send an email to everyone that purchased the Beta 2 Kit? I have clicked "Check for Updates" but there is no information there. All I get is "All your office products are up to date". Thanks for reading. Hi Robin, I have the following URL to view, nothing more. http://officebeta.microsoft.com/home/default.aspx I rea...

Execute code by changing a Cell
Dear all, I need that excel execute some VBA code when I change the value of a cell in a sheet... How can I do it? Thanks a lot !!! Andr=E9. right click sheet tab>view code>left window select worksheet>right window select worksheet_change write code. You probably will want to restrict to a cell or range. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <gatarossi@ig.com.br> wrote in message news:1186925850.865616.124550@i13g2000prf.googlegroups.com... Dear all, I need that excel execute some VBA code when I change the value of a cell in ...

Problem with pasting special merged cells to merged cells
I see several posts on this topic but none recently and I have yet to find a solution. I am pasting special from a cell that consists to two merged cells to another cell in the same row that also consists of two merged cells. The columns are labeled Previous Month's Total and Current Month's Total so you can see what I'm trying to do. Interestingly, the problem seems to occur only when I'm doing a paste special Values. I have to do Values because the cell in the Current Month's Total contains an equation which I do not want to move to the cell in the Previous Mon...

series of cells in a column
is there any easy way of entering a long list of cells into a formul all in the same column? A1&A2&A3 continuing to A100. thank -- ROL ----------------------------------------------------------------------- ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606 View this thread: http://www.excelforum.com/showthread.php?threadid=27534 just enter it as A1:A100 HTH -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Cut, Copy, Paste, PasteSpecial grayed out.
Puzzling problem: In Excel both under "Edit" and in the shortcut menu "Cut", "Copy", "Paste" and "PasteSpecial" are grayed out. Just started today.Files are not protected. These commands work fine in Word and Outlook. Have closed and re-opened Excel. and Rebooted the computer to no avail. Can someone help me? Excel 2003,(Office 2003 Pro) Windows XP Pro SP3 Tablet Edition. Can you check to see if Ctrl+C and Ctrl+V work? if not it sounds as though they may have been prevented via code. You could check to see if the w...

selecting and copying visible cells only
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need help with the following: <br> Selecting, copying and pasting visible cells only > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3346128734_25432005 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 1/12/10 7:27 AM, in article 59bb0b9c.-1@webcrossing.JaKIaxP2ac0, "DVanBruggen@officeformac.com" <DVanBruggen@officeformac.com> wrote: > Version: 2008 ...

Cell size #2
I have a text box in an Excel worksheet that only accepts about 200 characters of text. Is there a way to expand the cell to include additional text? I get around the problem by splitting the text into multiple cells, but this is inconvenient and messy. A cell can contain up to 32k characters of text. I'm not sure what the limit of a Drawing toolbar Text Box or a Control Toolbox Text Box is but it is many more than 200 characters. If you're using the latter make sure the wordwrap and multiline properties are set on. -- Jim Rech Excel MVP "Dale Wares" <Dale War...

Copying smartlist folder
Is it possible copy a smartlist folder and save it with a different name? (like Sales Line Items copy to SOP Line Items). davidv Not through GP. Table Dynamics.asitab30 stores the information so you could change the data behind the scenes, if you want. -- Charles Allen, MVP "tintin91" wrote: > Is it possible copy a smartlist folder and save it with a different name? > (like Sales Line Items copy to SOP Line Items). > > davidv ...

Copy a user (InetOrPerson) in ADDS
Hello, I would like COPY a user to a new user in Active Directory. I tried the QAD (get-qaduser ... | new-qaduser...) snapin but my users are InetOrgPerson ObjectClass and I don't found method. Thanks for help ;) -- MiKL ...

copy a workbook without copying any data
I there a way to copy a workbook without copying any data. I would like to copy all tabs,micros,links and formulas without any data in the cells or notes for the cells being copied. Thank You Hi Rusty, One way: Sub Tester() Dim sh As Worksheet Dim strName As String strName = "ABCD.xls" '<<==== CHANGE ActiveWorkbook.SaveCopyAs strName Workbooks.Open Filename:= _ Application.DefaultFilePath & "\" & strName For Each sh In Workbooks(strName).Worksheets On Error Resume Next sh.Cells.SpecialCells(x...

Form problems in a table with merged cells
I have a spreadsheet I've been using for several years in which I had set up a ribbon button to show a data entry form using the table headers. I merged some cells in an area adjacent to the table and the form would no longer work. Clicking the ribbon button simply gave the message that the command could not be used in a table or range containing merged cells. I unmerged the offending cells but it still doesn't work. I'm stumped - can anyone please help> -- John ...

Insert copied row into a list
In Excel 2003, I can insert a row into a list by inserting a row into the spreadsheet. That is, I can right-click along the row numbers along the left, then insert a row. However, if I try to insert a copied a spreadsheet row, I get the message that I am trying to shift cells down within the list. That is, I select a row by clicking the row number on the left, press ctrl- C, then right-click another row on its row number at the left, and choose insert copied row (or maybe insert pasted row -- I'm don't have access to Excel at the moment to verify). Inserting a copied/pasted row doe...

validation error msg for calculated cells
I have been unable to create a data validation error msg for calculated values in cells. It would be nice to be able to get that pop-up effect. Anyone know how to do this? Thanks, JR Data|Validation is for stopping typing errors--not formula errors. Maybe you can use an adjacent cell (nice bold red letters) that shows a warning if the formula evaluates to something you don't want. JR wrote: > > I have been unable to create a data validation error msg > for calculated values in cells. > > It would be nice to be able to get that pop-up effect. > > Anyone know...

Copying Contact data
Hello, I have Outlook 2002 SP-2 and I had to reinstall it on a new HDD (The old one is OK except that it won't boot) I have reinstalled OK but I need to copy the contact data from the old drive to the new one. What is the file called that stores this data. I have tried importing but it only brings over email addresses - and not the names and addresses that are in the contacts section of the program. Mekon. You tried importing what? All Outlook data is stored in the same file. The file you need is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc ar...

format cells for Currency in catalog merge
I've created a single page publisher document that is intended to display prices of a certain vendor's items. To do this, I merged several fields from an Access query into the document and filtered the records I wanted. This worked fine (for the most part). However, Access formatting was not carried into the document, and my currency valuesa re not recognizable as such. I can't find anything in Help that describes how to format a table cell for currency values. Any ideas? Thanks, Randall Arnold Export your data from Access into a CSV (Comma Separated Values) and use the CS...

receiving multiple copies of emails
I have Outlook 2002 SP2 and I'm receivig multiple copies of emails (between 2 and 10 copies of the same message). My POP3 mail server administrator says that my Outlook is damaged and is not sending receipt acknowledgements to the server so that the server never deletes the sent messages and continues send them over and over again. How can I fix this porblem? Please respond to adddress listed. Many thanks I have a client that is having the same issue and I have been troubleshooting it for 2 days and can't find anything on microsoft's website. Have you done a windows upd...