How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol

Carol,

If you put a "$" directly before the column reference in the formula (for
example, $F32), the column reference will not change. Likewise, a $ before
the row reference makes it not alter.

I'm not sure about your other question, sorry.

Mark

"englishrose4719" wrote:

How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol

thanks Mark, but Im still a bit confused - i have a horrendously long formula
thats got ifs/ors/ands/counts in it - when do i put the $ sign in, while
writing out the formula? Then how do i go about changing the worksheet
number?

Carol

"Mark Brown" wrote:

Carol,

If you put a "$" directly before the column reference in the formula (for
example, $F32), the column reference will not change. Likewise, a $ before
the row reference makes it not alter.

I'm not sure about your other question, sorry.

Mark

"englishrose4719" wrote:

How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol

You need to use absolute cell references by selecting the cell you need to
absolute, press the F4 key to absolute the column and the row, or continue to
press F4 for a partial reference, referencing only the column or the row. You
should now be able to copy the formula.

"englishrose4719" wrote:

thanks Mark, but Im still a bit confused - i have a horrendously long formula
thats got ifs/ors/ands/counts in it - when do i put the $ sign in, while
writing out the formula? Then how do i go about changing the worksheet
number?

Carol

"Mark Brown" wrote:

Carol,

If you put a "$" directly before the column reference in the formula (for
example, $F32), the column reference will not change. Likewise, a $ before
the row reference makes it not alter.

I'm not sure about your other question, sorry.

Mark

"englishrose4719" wrote:

How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol

On your second question, select and copy the data in the first sheet, ensure
that the active cell is in A1 (If you started from A1) in alll the other
sheets. Now click the first sheet tag, click the navigator at the bottom left
of the screen so that you can see the last sheet, hold down the shift key and
click the last sheet, switching on group mode. Now paste in the normal way

"englishrose4719" wrote:

How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol

Wow, that was easy - thank you.

Not so sure about the absolute refernces tho - can i still change the sheet
number in the formula or will that stay fixed if ive F4'd the formula. Is
there an easy way to get the formula to +1 on every time i paste to update
the sheet number. Otherwise ive got to update it 90 odd times!!!!

"Simon Pope" wrote:

On your second question, select and copy the data in the first sheet, ensure
that the active cell is in A1 (If you started from A1) in alll the other
sheets. Now click the first sheet tag, click the navigator at the bottom left
of the screen so that you can see the last sheet, hold down the shift key and
click the last sheet, switching on group mode. Now paste in the normal way

"englishrose4719" wrote:

How do I copy a formula along 31 columns in a summary spreadsheet without it
altering the column references? Because the data is coming from identical
spreadsheets but on different days of the month i need only the sheet number
to change.

Also is there a quick way to copy and paste the first formulated spreadsheet
across to the 30 others in one hit? Ive tried a couple of ways already
suggested but didnt get very far.

Thanks lots
Carol