Hi

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.

Hi, 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 =$A2similarly =A$1 dragged right becomes = B$1

but dragged down it stays as = A$1and =$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.Regards

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 P10I 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.

Hi, 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.Regards

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.

Birdbrain

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History