Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy formula with row automatically modified not column
Hi,
I am trying to figure out a means to copy a formula across multiple columns so that the row value increments automatically but not the column value. For example: The first cell, A1, has a formula of: =Sheet1!K110 After copy/paste, Cell B1 should have: =Sheet1!K114 After copy/paste, Cell C1 should have: =Sheet1!K118 After copy/paste, Cell B1 should have: =Sheet1!K122 The sheet I am working on has probably 200 such cells so being able to do this via copy/paste would be ideal compared to editing each cell one-by-one. Thank you for your help. PS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy formula with row automatically modified not column
Try this:
=INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3) Copy across as needed. Adjust the end of range: $K300 as needed. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Hi, I am trying to figure out a means to copy a formula across multiple columns so that the row value increments automatically but not the column value. For example: The first cell, A1, has a formula of: =Sheet1!K110 After copy/paste, Cell B1 should have: =Sheet1!K114 After copy/paste, Cell C1 should have: =Sheet1!K118 After copy/paste, Cell B1 should have: =Sheet1!K122 The sheet I am working on has probably 200 such cells so being able to do this via copy/paste would be ideal compared to editing each cell one-by-one. Thank you for your help. PS |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy formula with row automatically modified not column
Hi Biff,
Thanks alot, this worked great! PS "T. Valko" wrote: Try this: =INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3) Copy across as needed. Adjust the end of range: $K300 as needed. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Hi, I am trying to figure out a means to copy a formula across multiple columns so that the row value increments automatically but not the column value. For example: The first cell, A1, has a formula of: =Sheet1!K110 After copy/paste, Cell B1 should have: =Sheet1!K114 After copy/paste, Cell C1 should have: =Sheet1!K118 After copy/paste, Cell B1 should have: =Sheet1!K122 The sheet I am working on has probably 200 such cells so being able to do this via copy/paste would be ideal compared to editing each cell one-by-one. Thank you for your help. PS |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy formula with row automatically modified not column
Hi,
Here is another option: =INDIRECT("Sheet1!K"&106+COLUMN()*4) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul" wrote: Hi, I am trying to figure out a means to copy a formula across multiple columns so that the row value increments automatically but not the column value. For example: The first cell, A1, has a formula of: =Sheet1!K110 After copy/paste, Cell B1 should have: =Sheet1!K114 After copy/paste, Cell C1 should have: =Sheet1!K118 After copy/paste, Cell B1 should have: =Sheet1!K122 The sheet I am working on has probably 200 such cells so being able to do this via copy/paste would be ideal compared to editing each cell one-by-one. Thank you for your help. PS |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy formula with row automatically modified not column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Paul" wrote in message ... Hi Biff, Thanks alot, this worked great! PS "T. Valko" wrote: Try this: =INDEX(Sheet1!$K110:$K300,COLUMNS($A1:A1)*4-3) Copy across as needed. Adjust the end of range: $K300 as needed. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Hi, I am trying to figure out a means to copy a formula across multiple columns so that the row value increments automatically but not the column value. For example: The first cell, A1, has a formula of: =Sheet1!K110 After copy/paste, Cell B1 should have: =Sheet1!K114 After copy/paste, Cell C1 should have: =Sheet1!K118 After copy/paste, Cell B1 should have: =Sheet1!K122 The sheet I am working on has probably 200 such cells so being able to do this via copy/paste would be ideal compared to editing each cell one-by-one. Thank you for your help. PS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically show modified date? | Excel Worksheet Functions | |||
How to automatically copy VLOOKUP FORMULA IN A COLUMN | Excel Discussion (Misc queries) | |||
Copy formula automatically down when date is entered | Excel Discussion (Misc queries) | |||
Create a copy in the same folder when a file is modified and saved | Excel Discussion (Misc queries) | |||
Automatically insert row and copy formula | Excel Worksheet Functions |