Indirect function

Posted on 2011-05-02
Last Modified: 2012-05-11
earlier in this thread, you proviided an example that allowed me to use the indirect funtion to draw data into a master spreadsheet; this worked well.  Now I have run into a situation, where I'd like to draw and sum numerical data from my referenced worksheets, but i'm finding that the references remain absolute in the master; i

Speciifically,  when, on one of the referenced sheets, I insert a line (to add new numerical data), the new celll (now one line lower) is not referenced anymore from the master; it has remained the same and has not adjusted.  This is a hard question to word.  Hopefully makes some sense.  Thanks in advance

Question by:starport
    LVL 39

    Expert Comment

    As stated in the previous question

    will give the value in A1 of the sheet whose name is in A1 of the current sheet.

    If you want the row to follow when you copy down, you can use something like
    =indirect($A$1&"!a" & row())

    LVL 43

    Accepted Solution

    you can try something like


    where the bold text should be replaced with values or cell addresses which contain values.

    LVL 6

    Expert Comment

    by:Eric Zwiekhorst
    Dear starport,

    The reason why indirect exists is the static behaviour.
    If you dant you cells to follow insertin an deleting cells please use you formula without the indirect and it will do jst that..

    Kind regards


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    728 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