ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Expanding spreadsheet with formulas (https://www.excelbanter.com/excel-discussion-misc-queries/182826-expanding-spreadsheet-formulas.html)

Redsphynx

Expanding spreadsheet with formulas
 
Hi,

I have a very large spreadsheet, and I need to add another 20 columns in two
places (20 x 2) but I have formulas I would like to carry across with them.

In the first group I have a formula like this:

=AT8/C8 AT8=current column and C8=fixed cell and is the same in every
column.

Is there a way to insert a block of columns and they carry the C8 but change
the AT8 to their own column number??

Second group is a bit more complex....
=IF( X110, X11*F11," ")
X10 refers to column and row but F11 refers to fixed column but
corrisponding row.

I need to add another block of columns that will keep this.

There are about 260 rows in each of these columns, and I would like to do
this as simply as possible.

Alan

Expanding spreadsheet with formulas
 
To do the first bit, try :-
=AT8/$C$8
The $ signs prevent the cell reference changing when it's dragged across or
down.
Have a look in 'Help', enter absolute reference or relative reference and
it's all explained,
Regards,
Alan.
"Redsphynx" wrote in message
...
Hi,

I have a very large spreadsheet, and I need to add another 20 columns in
two
places (20 x 2) but I have formulas I would like to carry across with
them.

In the first group I have a formula like this:

=AT8/C8 AT8=current column and C8=fixed cell and is the same in every
column.

Is there a way to insert a block of columns and they carry the C8 but
change
the AT8 to their own column number??

Second group is a bit more complex....
=IF( X110, X11*F11," ")
X10 refers to column and row but F11 refers to fixed column but
corrisponding row.

I need to add another block of columns that will keep this.

There are about 260 rows in each of these columns, and I would like to do
this as simply as possible.



Redsphynx

Expanding spreadsheet with formulas
 
I have fudged my way through the second block by inserting the required
columns, then copying the top cell of each across all columns, then once I
have modified the F11 I then highlighted them all and Ctrl + C then
highlighted the remainder of the new block and Ctrl + V. Worked a charm.

I will try how you suggested for the first block, and let you know how I get
on.


"Alan" wrote:

To do the first bit, try :-
=AT8/$C$8
The $ signs prevent the cell reference changing when it's dragged across or
down.
Have a look in 'Help', enter absolute reference or relative reference and
it's all explained,
Regards,
Alan.
"Redsphynx" wrote in message
...
Hi,

I have a very large spreadsheet, and I need to add another 20 columns in
two
places (20 x 2) but I have formulas I would like to carry across with
them.

In the first group I have a formula like this:

=AT8/C8 AT8=current column and C8=fixed cell and is the same in every
column.

Is there a way to insert a block of columns and they carry the C8 but
change
the AT8 to their own column number??

Second group is a bit more complex....
=IF( X110, X11*F11," ")
X10 refers to column and row but F11 refers to fixed column but
corrisponding row.

I need to add another block of columns that will keep this.

There are about 260 rows in each of these columns, and I would like to do
this as simply as possible.





All times are GMT +1. The time now is 02:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com