+ Reply to Thread
Results 1 to 5 of 5

=INDIRECT, Sheet reference with Incremental cell reference

  1. #1
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    =INDIRECT, Sheet reference with Incremental cell reference

    Hi Everyone

    I've used the INDIRECT function several times, but never fully understood it.

    What I'm trying to do now is create 1 sheet with all the values from A2:A499 on 29 different sheets, Named Sheet1 - Sheet29.

    On my new sheet i'm using to compile the date, the headings in Row 1 are Sheet1 - Sheet29.

    Then in A2 I have =INDIRECT("'"&A$1&"'!$A2").

    This means, as I drag that equation around. the indirect reference will automatically adjust it's column reference but will always look at the value in row 1. and no matter what sheet is is looking at, it will always pull the value from column A, but adjust the rows accordingly

    The problem I have is that the cell reference value "$A2" is not changing. as I copy the equation to different cells. It always stays as "$A2". Even if I manially adjust to "$A3" & "$A4", Then select those three cells and drag the selection down to copy, all this does is repeat that same sequence of 3, it doesn't continue the count.

    Any ideas what I can do?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: =INDIRECT, Sheet reference with Incremental cell reference

    Oops, my mistake, ignore this.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: =INDIRECT, Sheet reference with Incremental cell reference

    Hi there,
    the cell reference in You example is static, since You are declaring it in between " sign.

    If You are dragging down and want to receive values from changing rows, You have to create dynamic row number.
    For example see attachement, with this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where in A1 is sheet name '1', so I get value from sheet '1' cell A1 (row A2=2 minus 1).
    Book1.xlsx
    If You want also dynamic columns, You have to combine it with another INDIRECT or OFFSET.

    Hope it helps
    Regards
    Miroslav R.

    (If You like my solutions, feel free to add reputation.)

  4. #4
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: =INDIRECT, Sheet reference with Incremental cell reference

    Thanks Miroslav, that works perfectly (with a slight adjustment adding another $ to affix the cell ref column)

    =INDIRECT("'"&A$1&"'!A"&ROW($A3)-1)

    I'll be honest though, I don't understand "why" this works, but a big part of that is me not understanding Indirect fully, or Offset at all is offset the bit,

    Why did you need the "(A2)-1", Whay couldnt you just put "(A1)"? As I've tested and that works too.

  5. #5
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: =INDIRECT, Sheet reference with Incremental cell reference

    Hi Deap,
    of course You can use A1 instear of A2-1. The result is the same...
    It is just because when I create formulas with ROW or COLUMN functions, I like to be fixed to the cell I enter the formula in. So it is only 'my way' of doing things.

    Happy that it hepls You!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  2. [SOLVED] How to reference an entire row based on an indirect cell reference
    By echo_oscar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-31-2012, 08:50 PM
  3. Sheet cell reference and INDIRECT()
    By Lithium in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-09-2009, 11:04 AM
  4. Reference a cell from an another sheet which is incremental
    By pad5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2008, 11:08 AM
  5. indirect function to reference cell on different sheet
    By Dolemite in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-06-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1