Copying formulas so they change by column not

Microsoft Excel 2003 (full product)
February 14, 2010 at 04:56:26
Specs: Windows XP
I am working in Excel 2007 but am saving the file as Excel 97-2003 as I am doing it for a friend who only has 2003. I have a large multisheet workbook which records staff names and dates when training courses are undertaken - a separate worksheet for each year. Then I have worksheets which are for detailed and summary management reports plus individual training reports - the formulas I am putting in automatically pull the information through. As I have got a mass of formulas to put in I want to be able to click on them and copy them down and across but I can't get a formula right to do this.

Basically I have names in column A other information in other columns including all the courses so the information for one person runs across the worksheet. When you copy cells it assumes you are going vertically. Is there a way I can copy formulas and have the columns autuomatically update?
All help gratefully received.

See More: Copying formulas so they change by column not

Report •

February 14, 2010 at 05:22:25

It would be helpful to see one of the formulas that does not update properly.

In general terms the $ sign in formulas identifies the row or column as fixed in the formula

=A1 can be dragged to the right to become =B1
or dragged down to become =A2

=$A1 dragged right stays as =$A1
but dragged down it becomes =$A2

similarly =A$1 dragged right becomes = B$1
but dragged down it stays as = A$1

and =$A$1 doesn't change when dragged or copied.

Hope this sheds some light on the issue - but please post an example preferably with an example of what you want it to be after dragging or copying it.


Report •

February 15, 2010 at 00:37:44
Hello again - thank you for replying! I am trying to set the spreadsheet up to do the following:

Course 1 Info from Sheet "2009 Data" cell M10
Course 2 Info from Sheet "2009 Data" cell N10
Course 3 Info from Sheet "2009 Data" cell O10
Course 4 Info from Sheet "2009 Data" cell P10

I don't know how to set up the following formula so that the column changes as I copy it down to other cells, but not the row:

='2009 Data'!M10

Thank you hopefully.

Report •

February 15, 2010 at 05:09:37

Here is a solution. The formula uses the row that the formula is on to create an offset.
Enter this formula in a cell in Row 6

=OFFSET('2009 Data'!$M$10,0,ROW()-6)

If you wish to start in another row, replace 6 with the row number that the formula is in.
This means that the first formula you enter returns the value in cell M10 on sheet '2009 Data'. The Offset is zero for both row and column.
Note the $ in the formula
Drag the formula down as many rows as required.
The formula looks identical in all rows, but the column offset value is increasing with the increasing row number.


Report •

Related Solutions

February 15, 2010 at 05:39:03
Hello again
Wow - that's fantastic - I have just tried it - brilliant. Thank you so much - you have no idea how much time you have just saved me. There may be more questions before I finish this workbook I am afraid!
Once again, thank you.

Report •

Ask Question