Solved

# copy referenced cell and increment by one row

Posted on 2011-10-10
185 Views
Spreadsheet Div1, column AN, has data in evey other row starting with row 5.  I want to copy the data to another spreadsheet named Place.  In Place, cell E3 I have entered the standard reference formula.

='Div1'!AN5

Is it possible (wihtout using VBA)to copy the reference to the remaining cells in column E incrementing by one row?

Desired results:

Place E3 ='Div1'!AN5
Place E4 ='Div1'!AN7
Place E5 ='Div1'!AN9

and so on.
0
Question by:Lindahq

LVL 43

Accepted Solution

Try something like

=offset('Div1'!\$AN\$5,(row()-3)*2,0)

in E3 and then copy down
0

Author Closing Comment

THANK YOU!!!!!!
0

Author Comment

I followed the solution provided and it worked for rows 5 through 25, but the spreadsheet goes to row 57.  After row 25 the result is #N/A.  Why?
0

LVL 43

Expert Comment

Check the spreadsheet and make sure that the numbers are all in odd numbered rows.
0

## Featured Post

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …