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?
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
All help gratefully received.
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