copy referenced cell and increment by one row

Posted on 2011-10-10
Last Modified: 2012-08-13
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.


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.
Question by:Lindahq
    LVL 43

    Accepted Solution

    Try something like


    in E3 and then copy down

    Author Closing Comment

    THANK YOU!!!!!!

    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?
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Check the spreadsheet and make sure that the numbers are all in odd numbered rows.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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 …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now