Copy function
I am looking to copy a formula for a vertical array of data while referencing
the same column. I know that adding two $ before and after a cell letter references that particular cell each time the formula is copied; however, i want to reference the same column, not a particular cell. Also, I am working with over 45,000 data points for 12 different worksheet; how do I copy a formula down an entire worksheet without having to drag the little black box at the bottom of a cell? This will help GREATLY Thanks! Scott |
Copy function
Hi
Double click the little black box (Fill handle) and the formula will fill down as far as there are data in the adjacent column. Mike "scott" wrote: I am looking to copy a formula for a vertical array of data while referencing the same column. I know that adding two $ before and after a cell letter references that particular cell each time the formula is copied; however, i want to reference the same column, not a particular cell. Also, I am working with over 45,000 data points for 12 different worksheet; how do I copy a formula down an entire worksheet without having to drag the little black box at the bottom of a cell? This will help GREATLY Thanks! Scott |
Copy function
Is there not a keyboard shortcut that is available? I noticed that using the
black box works only when the data in a column is right next to a column with data in it. How about if you need to copy a formula down a spreadsheet that is not directly next to a column with data, especially when large amounts of data are involved, such as I am dealing with? "Mike H" wrote: Hi Double click the little black box (Fill handle) and the formula will fill down as far as there are data in the adjacent column. Mike "scott" wrote: I am looking to copy a formula for a vertical array of data while referencing the same column. I know that adding two $ before and after a cell letter references that particular cell each time the formula is copied; however, i want to reference the same column, not a particular cell. Also, I am working with over 45,000 data points for 12 different worksheet; how do I copy a formula down an entire worksheet without having to drag the little black box at the bottom of a cell? This will help GREATLY Thanks! Scott |
Copy function
A $ before the column letter indicates absolute column reference
A $ before the row number indicates absolute row reference. In your case it sounds as though you want only the first $ before the column number. There is no single shortcut key for copying to the bottom of the range. You can Ctrl-v to copy a cell or range of cells, then press Ctrl-down arrow to go the next non-blank cell in the column - or to the bottom of the sheet if you're in the last non-blank cell. Other key combo would be Shift-Ctrl-End to get to the last used cell in the sheet "scott" wrote: Is there not a keyboard shortcut that is available? I noticed that using the black box works only when the data in a column is right next to a column with data in it. How about if you need to copy a formula down a spreadsheet that is not directly next to a column with data, especially when large amounts of data are involved, such as I am dealing with? "Mike H" wrote: Hi Double click the little black box (Fill handle) and the formula will fill down as far as there are data in the adjacent column. Mike "scott" wrote: I am looking to copy a formula for a vertical array of data while referencing the same column. I know that adding two $ before and after a cell letter references that particular cell each time the formula is copied; however, i want to reference the same column, not a particular cell. Also, I am working with over 45,000 data points for 12 different worksheet; how do I copy a formula down an entire worksheet without having to drag the little black box at the bottom of a cell? This will help GREATLY Thanks! Scott |
Copy function
$A1 will reference the same column but the row will increment.
Easiest way to copy down in a column with no adjacent data is to use the namebox Assume your formula is in A1 In namebox type A1:A45000 and ENTER CTRL + d to copy down. Gord Dibben MS Excel MVP On Thu, 14 Aug 2008 11:54:12 -0700, scott wrote: Is there not a keyboard shortcut that is available? I noticed that using the black box works only when the data in a column is right next to a column with data in it. How about if you need to copy a formula down a spreadsheet that is not directly next to a column with data, especially when large amounts of data are involved, such as I am dealing with? "Mike H" wrote: Hi Double click the little black box (Fill handle) and the formula will fill down as far as there are data in the adjacent column. Mike "scott" wrote: I am looking to copy a formula for a vertical array of data while referencing the same column. I know that adding two $ before and after a cell letter references that particular cell each time the formula is copied; however, i want to reference the same column, not a particular cell. Also, I am working with over 45,000 data points for 12 different worksheet; how do I copy a formula down an entire worksheet without having to drag the little black box at the bottom of a cell? This will help GREATLY Thanks! Scott |
All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com