Copying formulas

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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!!!!