copying formulas #9

I am trying to copy formulas in a spreadsheet that pulls the data from every 
4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, 
n6). When I try to copy the pattern to the next 4 cells in the row, 
anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I 
correct this?
0
Tracey (42)
12/21/2004 8:47:06 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
653 Views

Similar Articles

[PageSpeed] 4

One way

=OFFSET(Sheet2!$B$6,,COLUMN(A:A)*4-4)

copy across


Regards,

Peo Sjoblom

"Tracey" wrote:

> I am trying to copy formulas in a spreadsheet that pulls the data from every 
> 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, 
> n6). When I try to copy the pattern to the next 4 cells in the row, 
> anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I 
> correct this?
0
PeoSjoblom (789)
12/21/2004 9:19:01 PM
Hi
in the first cell (lets assume A1) enter
=OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
and copy to the right

-- 
Regards
Frank Kabel
Frankfurt, Germany
"Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag 
news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
>I am trying to copy formulas in a spreadsheet that pulls the data from 
>every
> 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6,
> n6). When I try to copy the pattern to the next 4 cells in the row,
> anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I
> correct this? 


0
frank.kabel (11126)
12/21/2004 9:20:33 PM
Thank you. The OFFSET command identifies the correct cell. However, when I 
copy to the right, the pattern doesn't repeat. It always remains anchored to 
the first cell (eg. A1), so all subsequent results repeat the same value. I 
tried removing the $ before the column, but it still doesn't work. I need the 
copied formula to move the anchor point over by 4 columns as the new 
reference. How do I do that?

"Frank Kabel" wrote:

> Hi
> in the first cell (lets assume A1) enter
> =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
> and copy to the right
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> "Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
> >I am trying to copy formulas in a spreadsheet that pulls the data from 
> >every
> > 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6,
> > n6). When I try to copy the pattern to the next 4 cells in the row,
> > anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I
> > correct this? 
> 
> 
> 
0
Tracey (42)
12/22/2004 1:55:03 PM
Hi
this is done automatically by the thirdparameter of this formula: 
(COLUMN()-1)*4

So $A$1 is correct. The third parameter shifts the cell offset

-- 
Regards
Frank Kabel
Frankfurt, Germany

Tracey wrote:
> Thank you. The OFFSET command identifies the correct cell. However,
> when I copy to the right, the pattern doesn't repeat. It always
> remains anchored to the first cell (eg. A1), so all subsequent
> results repeat the same value. I tried removing the $ before the
> column, but it still doesn't work. I need the copied formula to move
> the anchor point over by 4 columns as the new reference. How do I do
> that?
>
> "Frank Kabel" wrote:
>
>> Hi
>> in the first cell (lets assume A1) enter
>> =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
>> and copy to the right
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> "Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
>>> I am trying to copy formulas in a spreadsheet that pulls the data
>>> from every
>>> 4th column in another spreadsheet in a consistent pattern (eg. b6,
>>> f6, j6, n6). When I try to copy the pattern to the next 4 cells in
>>> the row, anticipating r6, v6, etc. instead it repeats as f6, j6,
>>> n6, r6. How do I correct this? 


0
frank.kabel (11126)
12/22/2004 2:00:09 PM
Thanks again. However, I'm still not getting there. Here is exactly what I am 
typing:

=OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4

What have I misinterpreted?

"Frank Kabel" wrote:

> Hi
> this is done automatically by the thirdparameter of this formula: 
> (COLUMN()-1)*4
> 
> So $A$1 is correct. The third parameter shifts the cell offset
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> Tracey wrote:
> > Thank you. The OFFSET command identifies the correct cell. However,
> > when I copy to the right, the pattern doesn't repeat. It always
> > remains anchored to the first cell (eg. A1), so all subsequent
> > results repeat the same value. I tried removing the $ before the
> > column, but it still doesn't work. I need the copied formula to move
> > the anchor point over by 4 columns as the new reference. How do I do
> > that?
> >
> > "Frank Kabel" wrote:
> >
> >> Hi
> >> in the first cell (lets assume A1) enter
> >> =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
> >> and copy to the right
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >> "Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag
> >> news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
> >>> I am trying to copy formulas in a spreadsheet that pulls the data
> >>> from every
> >>> 4th column in another spreadsheet in a consistent pattern (eg. b6,
> >>> f6, j6, n6). When I try to copy the pattern to the next 4 cells in
> >>> the row, anticipating r6, v6, etc. instead it repeats as f6, j6,
> >>> n6, r6. How do I correct this? 
> 
> 
> 
0
Tracey (42)
12/22/2004 3:11:06 PM
Hi
use:
=OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN(A:A)-1)*4)

-- 
Regards
Frank Kabel
Frankfurt, Germany

Tracey wrote:
> Thanks again. However, I'm still not getting there. Here is exactly
> what I am typing:
>
> =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4
>
> What have I misinterpreted?
>
> "Frank Kabel" wrote:
>
>> Hi
>> this is done automatically by the thirdparameter of this formula:
>> (COLUMN()-1)*4
>>
>> So $A$1 is correct. The third parameter shifts the cell offset
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> Tracey wrote:
>>> Thank you. The OFFSET command identifies the correct cell. However,
>>> when I copy to the right, the pattern doesn't repeat. It always
>>> remains anchored to the first cell (eg. A1), so all subsequent
>>> results repeat the same value. I tried removing the $ before the
>>> column, but it still doesn't work. I need the copied formula to move
>>> the anchor point over by 4 columns as the new reference. How do I do
>>> that?
>>>
>>> "Frank Kabel" wrote:
>>>
>>>> Hi
>>>> in the first cell (lets assume A1) enter
>>>> =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
>>>> and copy to the right
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>> "Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag
>>>> news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
>>>>> I am trying to copy formulas in a spreadsheet that pulls the data
>>>>> from every
>>>>> 4th column in another spreadsheet in a consistent pattern (eg. b6,
>>>>> f6, j6, n6). When I try to copy the pattern to the next 4 cells in
>>>>> the row, anticipating r6, v6, etc. instead it repeats as f6, j6,
>>>>> n6, r6. How do I correct this? 


0
frank.kabel (11126)
12/22/2004 3:16:58 PM
Got it! Thank you so much!

"Frank Kabel" wrote:

> Hi
> use:
> =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN(A:A)-1)*4)
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> Tracey wrote:
> > Thanks again. However, I'm still not getting there. Here is exactly
> > what I am typing:
> >
> > =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4
> >
> > What have I misinterpreted?
> >
> > "Frank Kabel" wrote:
> >
> >> Hi
> >> this is done automatically by the thirdparameter of this formula:
> >> (COLUMN()-1)*4
> >>
> >> So $A$1 is correct. The third parameter shifts the cell offset
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >> Tracey wrote:
> >>> Thank you. The OFFSET command identifies the correct cell. However,
> >>> when I copy to the right, the pattern doesn't repeat. It always
> >>> remains anchored to the first cell (eg. A1), so all subsequent
> >>> results repeat the same value. I tried removing the $ before the
> >>> column, but it still doesn't work. I need the copied formula to move
> >>> the anchor point over by 4 columns as the new reference. How do I do
> >>> that?
> >>>
> >>> "Frank Kabel" wrote:
> >>>
> >>>> Hi
> >>>> in the first cell (lets assume A1) enter
> >>>> =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4)
> >>>> and copy to the right
> >>>>
> >>>> --
> >>>> Regards
> >>>> Frank Kabel
> >>>> Frankfurt, Germany
> >>>> "Tracey" <Tracey@discussions.microsoft.com> schrieb im Newsbeitrag
> >>>> news:A3B25F9D-36D7-4132-AB28-C9BF20E1E9F6@microsoft.com...
> >>>>> I am trying to copy formulas in a spreadsheet that pulls the data
> >>>>> from every
> >>>>> 4th column in another spreadsheet in a consistent pattern (eg. b6,
> >>>>> f6, j6, n6). When I try to copy the pattern to the next 4 cells in
> >>>>> the row, anticipating r6, v6, etc. instead it repeats as f6, j6,
> >>>>> n6, r6. How do I correct this? 
> 
> 
> 
0
Tracey (42)
12/22/2004 3:51:07 PM
Reply:

Similar Artilces:

Copy and Paste not saving format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I just bought Microsoft Office for Mac early this month and am still getting used to it. Can someone help me with this: I have several documents typed onto a notebook layout with lots of bullet points, color changes, cross outs and the like. I wanted to copy and past all of that into a new document but when I did ALL of the formatting changes were lost. There were no bullet points, so the text flushed left, yada, yada, yada. I looked around the toolbar to try and find a setting that would allow me to do the cut an...

EzPaste-xl2ppt Excel add-in, automating the copy/paste of charts and data
Hello, This is to notify about the release of EzPaste (http:// www.EzPaste.net), an Excel add-in intended to completely automate the transfer of charts and data from Excel to PowerPoint. EzPaste identifies automatically all the charts and the tables defined in the active workbook. The user then selects which of them he wants to paste to PowerPoint=AE , and EzPaste does all the work, would you have to paste one or one hundred tables/charts at once Even if you have to paste the selected range or chart, EzPaste does it with the click of a button Sorry if this message is a bit advertising, b...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

Copying contacts?
Hi I am trying to copy my Contacts to an other PC using flash memory. For some reason no success? I seam to copy a lot extra on top of Contacts and therefore cannot proceede to the end of the .pst import wizard. Are there instructions to do that? Thanks Kalevi Yes. Never export or import. Copy the Contacts into a PST file. Open that PST file in the other installation. -- Russ Valentine [MVP-Outlook] "Kalevi" <kale@woodworld.fi> wrote in message news:dpakkj$aru$1@nyytiset.pp.htv.fi... > Hi > I am trying to copy my Contacts to an other PC using flash memory. For &...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

eService Call with GP 9.0
eService GP 8 release was unchanged for GP 9.0 as per Article ". eService & eReturns NOTE: eService & eReturns for Microsoft Dynamics GP 9.0 utilizes the same download as Microsoft Great Plains 8.0. There are no changes to eService & eReturns requiring an upgrade or installation difference. " I tried to configure eService for GP 9.0 but not able to view the proper page, it seems the COM components “Webservice” not working properly. Anyone come across the same situation? You need to go to Component Services, Computers, My Computers, Com+ Apps and make sure th...

What could keep formulas from recalculating?
I have a workbook that I received from a company with which I do business. On it are several worksheets (tabs). On one sheet, if I go to enter a formula in a cell, it displays it as text instead of calculating a value. For example, if I type =A1+A2 it will display that exact text in the cell as opposed to displaying the sum of cells A1 and A2. On other worksheets in the same workbook, entering formulas seems to work just fine. I have checked in Options, and auto-calc is checked (pressing F9 also has no effect). I can only assume that there is some setting of which I am unaware that is prev...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

Automated Copy Paste
I have a template with 7 sheets that I continuously update with new data that I get from a seprate file. Each sheet is named "sheet1", "sheet2" "sheet3" and so on. My template has a series of summary sheets based off of the data that lies in these 7 inputs sheets. Currently, I use copy, paste special to update sheets 1-7 in my template. Instead of using copy, paste special one at a time, I would like to the move 7 sheets from my other file in my template. If I do this, the sheets will show up as duplicates so I will have "sheet1 (2)"...

Macro Copy and Paste
I am currently trying to copy from one workbook that has 7 differen tabs and paste into one worksheet on a separate master workbook. M problem is that when I set up the macro to copy from one of the tabs i the workbook and paste to the other work book, I only want the the row to come in where there is data. Currently I am having to run a macr that takes every 100 lines, but ideally I only want the informatio copy and pasted where there is only data. In addition, once I have copy and pasted everything from the first tab my next step is to copy and paste information from the second tab t th...

I cannot paste from one workbook to another. Copy works, paste do.
When I attempt to copy from one workbook and paste into another, copy appears to work but paste does not. The paste menu item is grayed out in the workbook to which I attempt to paste. Are there setting that can be changed to aloow the paste?? Check to see if either workbook and/or worksheet is protected. If so, unprotect. Then Copy/Paste should work. BTW, how are you copy?paste(ing) Tab or content? Dennis "JimmyMc" wrote: > When I attempt to copy from one workbook and paste into another, copy appears > to work but paste does not. The paste menu item is grayed out...

how do I prevent a worksheet from being copied in excel
yet allow users to select values in a defined range Good evening kensanjose You really need to supply more information about what you mean - cop the file/sheets/ranges etc Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1893 View this thread: http://www.excelforum.com/showthread.php?threadid=38531 How do I prevent a worksheet from being copied in excel? Can I disable right click within a file so that the sheet cannot be copied into another file and manipula...

R-squared formula
I was just wondering if anybody knows the formula for the r-squared value for a power curve. Excel states that it uses a transformed r-squared value. I am trying to calculate the value by doing the mathematical calculations in the spreadsheet. I have gotten the correct equation that the chart shows, but I can't seem to get the correct r-squared value. The formulas that they give are: R^2 = 1-(SSE/SST) SSE = E(Yi-Yi^)^2 SST = (EYi^2)-(EYi)^2/n E is the best I can get to a sigma in this. Sigma is the sign to sum up all the indicated values. Yi is the original Y values. Yi^ ...

Excel formula #9
I need some help Please. not sure how to write a formula? Description of what I want.: if cell is equal to 48 or less then I want to add 3 to the cell but if cell is greater the 49 then I want to add 6 to cell Can some one help Please. I am new with excel and am not sure how to do this.. Thanks Sun, 3 Feb 2008 19:04:06 -0800 from David franklin <DavidF@discussions.microsoft.com>: > I need some help Please. not sure how to write a formula? > > if cell is equal to 48 or less then I want to add 3 to the cell but > if cell is greater the 49 then I want to add 6 to cell ...

GreatPlains 9.0 and SQL2005
I'm trying to find requirements for GP 8.0 (SQL2000 and BusinessPortal 2.5 ) to GP 9.0 (SQL2000 BusinessPoratl 3.0) upgrade. As far as I understand SQL 2005 not required to run GP 9.0 with BusinessPortal 3.0 . Am I going to lose any functionality/features buy staying with SQL2000? I heard windows authentication will be available in GP 9.0 only in SQL2005 configuration. Is it true? Thanks in advance. You do not have access to the checkboxes in user setup with SQL 2000. However, you maintain the use of SQL Server Reporting Services because GP 9 does not work with the SSRS in 2005...

Increasing Month only in formula
Kindly i need help on below: i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" in Cell "A2" and "=Mar!G$2" in Cell "A3" and so on by simple dragging. is that possible? On Thu, 11 Feb 2010 12:53:01 -0800, Malla <Malla@discussions.microsoft.com> wrote: >Kindly i need help on below: > >i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" >in Cell "A2" and "=Mar!G$2" in Cell "A3" and so o...

Help: Seting the value of another cell with a formula
I know that there's a way to make a cell a particular value based on the entries of a range, or array of cells, but is it possible to do the reverse, using only one formula in a cell. Here's what I'm trying to do: What I would like to do is set one of a range of cell to have a value based on the value in A2. Example: Cell A1 has a value of 2, A2 has a value of 2007. I want A10 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2008. I want A11 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2009. I want A12 to equal...

How Many Days Since... Formula -Help
Hi, can anyone help? I have a spreadsheet tracking several different Departments in our company and how often they have an accident. I need to track how many days they go without an accident. So if Dept A had their latest accident yesterday. And if someone opens the spreadsheet in 3 days, it should say "4 days w/o an accident for Dept A. I'm sure this can be done, I just have no idea how :roll: Thanks in advance, Scotty Assuming 8/5/2003 is the start date... =TODAY()-"8/8/2003"&" Days w/o an accident for Dept A" Lance >-----Original Message----- &...

copying .pst files to cd #2
i got a new computer, and i want to transfer my inbox and sent messages files from the old computer to the new one. however, my .pst archives do not show up in explorer or in my cd burner "explorer" and when i located the files through "find files" and then dragged them into the cd burner i/o box, it cannot see them and doesn't copy them onto the cd... how in the world do i do this seemingly simple thing: copying files onto a cd so i can transfer them to my new computer and then import them into my new computer's outlook? this is the most ridiculous thing ...

How do I keep a formula from changing if a row is added or deleted
I have a formulas referencing cells in another worksheet. When rows are added or deleted from the worksheet the formulas are either changed to skip the new row or receive an #REF! error when a row is deleted. Regardless of what happens in the worksheet I want the formulas to remain the same. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany cwolt wrote: > I have a formulas referencing cells in another worksheet. When rows > are added or deleted from the worksheet the formulas are either > changed to skip the new row or receive an #REF! error when a row is >...

Tracking changes to a cell containing formula
I want to track changes to cells because a formula calculates a ne value, not because i changed directly the value of a cell. the excel help indicate the following: Cells that change because a formula calculates a new value---> To fin cells that change due to recalculation, use the tools on the Auditin toolbar. How to do that? The formula auditing toolbar doesn't seem to hav something like that... Thanks for your tim -- Message posted from http://www.ExcelForum.com Hi have a look at 'Tools - Track changes' -- Regards Frank Kabel Frankfurt, Germany > I want to track ch...

TABS formula Is This Possible & How
I have 50 worksheets in work book each Tab has a different date Say Jan.31 & so on. So what i want to do is in cell B2 on each sheet have the Tab name. Is there a formula for this. Because when i change the tab name in future i want cell B2 to follow suit one way =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) -- Gary Keramidas Excel 2003 "Mike" <Mike@discussions.microsoft.com> wrote in message news:8F259216-B026-4E4D-BB3B-...

Copy of sent e-mails in customized folder
Hi, is there an easy way to make OL2007 saving sent e-mails as original in sent items and as copy in an user-defined folder? Using the OL-standard-function to save sent e-mails will only save the mail in the selected folder, but I need the sent mail in the sent items-folder, too. Thanx Carsten "Carsten" <Carsten@discussions.microsoft.com> wrote in message news:19EEF9A2-4965-466D-85CE-8E6F889CB70F@microsoft.com... > is there an easy way to make OL2007 saving sent e-mails as original in sent > items and as copy in an user-defined folder? > >...

Excel Visual Formula Help For Adding
I have a formula stating Range("G5000").End (x1up).Select and am wanting to add to this where in the selected cell you add 1 + the first cell up. Thank you 1) First of all it should read: XLUP and not X1UP. 2) Your - after correction - formula selects the last filled cell in a range. I'm not sure I understood the rest of your requext... Assuming that the last cell being selected. by the command - what do you want to do from this point and on...? Micky -- והמשך/י, נא, לקרוא את השורה הבאה: *********** אם תגובתי עזרה לחץ/י, נא, על <כן> בפס האופקי התחתון! ...