# Thread: Autofilling cells with data from another sheet (2002 SP2)

1. ## Autofilling cells with data from another sheet (2002 SP2)

I have a workbook with two sheets. Sheet 1 contains data in rows. Sheet 2 displays a subset of the data in sheet 1 (along with other stuff manually entered, plus stuff from elsewhere).

What I want is to enter a reference to the contents of cell x,y in sheet 1 into a cell in sheet 2 (i.e. =sheet1!x,y), and have the next three cells to the right in sheet 2 auto-fill with the contents of three other (always the same offset to the right) cells in the same row in sheet 1.

Should be simple, probably is, but I can't figure it out

2. ## Re: Autofilling cells with data from another sheet (2002 SP2)

One way is to use formulas like this in, say. cell D3

=IF(A3="", "", VLOOKUP(A3, Sheet1!\$A\$1:\$H\$100, 4, FALSE))

This formula says: look up the value from the cell in column A in the same row (if not empty), in first column of the range A1:H100 in Sheet1, and return the corresponding value from the 4th column.

3. ## Re: Autofilling cells with data from another sheet (2002 SP2)

You could use (if you start in A1 on Sheet2):
<pre>=OFFSET(Sheet1!\$A\$1,ROW()-1,COLUMN()-1)</pre>

Adjust the "1" in the rows and columns to match the startting row and column of the formula.

Steve

4. ## Re: Autofilling cells with data from another sheet (2002 SP2)

Hans, that does it exactly as I want - thanks very much indeed.

5. ## Re: Autofilling cells with data from another sheet (2002 SP2)

Steve, thanks. Hans' solution works better for my particular needs, but I appreciate your help.

6. ## Re: Autofilling cells with data from another sheet (2002 SP2)

Be aware that, for Hans' formula to work, the values in column A must be unique. If there are duplicate values, Hans' formula will always find and return the first one.

7. ## Re: Autofilling cells with data from another sheet (2002 SP2)

OK, I started using this and it works great. However...as I create versions of this workbook, I find that the references to Sheet1 remain pointing to the original workbook (V10 for the sake of argument) as opposed to the current one (say V12). How do I make the sheet reference keep pointing to sheet1 in the current workbook?

8. ## Re: Autofilling cells with data from another sheet (2002 SP2)

It depends on the way you create a new version. If you duplicate the workbook (the .xls file), or use File | Save As to create a copy, or if you copy both worksheets to a new workbook (in one go), the formulas should refer to Sheet1 in the current workbook. However, if you copy the worksheet with the formulas to a new workbook by itself, without copying Sheet1 at the same time, the formulas will refer to Sheet1 in the original workbook.