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

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

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

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

$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