+ Reply to Thread
Results 1 to 14 of 14

Indirect working on current cell, but not when I scroll down

  1. 02-02-2016, 03:49 PM #1
    bjcowen9000
    bjcowen9000 is offline
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Indirect working on current cell, but not when I scroll down

    I have this formula:

    =INDIRECT("'"&$C$1&"'!r2")

    In cell c2


    But when I scroll it to c3, I want the r2 to change to r3 but it stay as r2.

    How can I get it to change to r3 automatically? I need to do this for thousands of rows down, so I cant manually do each one.

    Any ideas?
    Reply With QuoteRegister To Reply
  2. 02-02-2016, 03:52 PM #2
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Indirect working on current cell, but not when I scroll down

    Remove the dollar sign next to 1

    =INDIRECT("'"&$C1&"'!r2")
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  3. 02-02-2016, 03:55 PM #3
    bjcowen9000
    bjcowen9000 is offline
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Indirect working on current cell, but not when I scroll down

    You misinterpreted. I dont want C1 to change to C2, I want r2 to change. C1 needs to stay the same
    Reply With QuoteRegister To Reply
  4. 02-02-2016, 04:00 PM #4
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Indirect working on current cell, but not when I scroll down

    Give this formula a try then. =INDIRECT("'"&$C$1&"'!r"&ROW())
    Reply With QuoteRegister To Reply
  5. 02-02-2016, 04:01 PM #5
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Indirect working on current cell, but not when I scroll down

    This is one way assuming that the data is starting in R2 and continues down the R column.
    If the formula is in row 1
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    If the formula is in row 4. The -value is 1 less than the row number of the formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  6. 02-02-2016, 04:05 PM #6
    bjcowen9000
    bjcowen9000 is offline
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Indirect working on current cell, but not when I scroll down

    thanks, but this does not seem to work either. It just returns 0's
    Reply With QuoteRegister To Reply
  7. 02-02-2016, 04:06 PM #7
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Indirect working on current cell, but not when I scroll down

    Can you post your workbook?
    Reply With QuoteRegister To Reply
  8. 02-02-2016, 04:10 PM #8
    bjcowen9000
    bjcowen9000 is offline
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Indirect working on current cell, but not when I scroll down

    Ok, I think it is attached. I attached a simpler version.

    I have two sheets. Sheet 1 and Reference

    On sheet 1, when I drag the formula in b2 down to b3, why does c2 not change to c3?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-02-2016, 04:15 PM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Indirect working on current cell, but not when I scroll down

    The formula in Sheet1!B2 should be
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 02-02-2016, 04:16 PM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Indirect working on current cell, but not when I scroll down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  11. 02-02-2016, 04:18 PM #11
    bjcowen9000
    bjcowen9000 is offline
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Indirect working on current cell, but not when I scroll down

    ok thanks, that fixed it!
    Reply With QuoteRegister To Reply
  12. 02-02-2016, 04:22 PM #12
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Indirect working on current cell, but not when I scroll down

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  13. 02-03-2016, 04:05 AM #13
    Indi_Ra
    Indi_Ra is offline
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Indirect working on current cell, but not when I scroll down

    Why not directly?

    =INDIRECT("'"&$B$1&"'!"&C2)
    Reply With QuoteRegister To Reply
  14. 02-03-2016, 05:42 AM #14
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Indirect working on current cell, but not when I scroll down

    Quote Originally Posted by Indi_Ra View Post
    Why not directly?

    =INDIRECT("'"&$B$1&"'!"&C2)

    Because the INDIRECT is building a reference to another sheet/cell. In this case, B1 has "Reference" in it which is used as the sheet name. The rest of the formula is building a cell reference. However, your version actually uses the contents of cell C2, C3, C4, etc., to build the overall reference. If cells C2, C3, C4, C5 and C6 actually contained "C2", "C3", "C4", "C5" and "C6", your version of the formula would also work but that's not the case in the sample workbook.



    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    X
    Reference
    Jiejenn
    Jiejenn
    newdoverman
    newdoverman
    TMS
    Indi_Ra
    2
    0
    1
    C2
    0
    0
    0
    1
    1
    1
    3
    0.001
    1
    C3
    0
    0
    0
    4
    4
    4
    4
    0.002
    1
    C4
    0
    0
    0
    9
    9
    9
    5
    0.003
    1
    C5
    0
    0
    0
    13
    13
    13
    6
    0.004
    1
    0
    0
    0
    188
    188
    #REF!
    7
    8
    Jiejenn
    =INDIRECT("'"&$B$1&"'!r2")
    9
    Jiejenn
    =INDIRECT("'"&$B$1&"'!r"&ROW())
    10
    newdoverman
    =INDIRECT("'"&$B$1&"'!r"&ROW()+1)
    11
    newdoverman
    =INDIRECT("'"&$B$1&"'!C"&ROW())
    12
    TMS
    =INDIRECT("'"&$B$1&"'!C"&ROW(C2))
    13
    Indi_Ra
    =INDIRECT("'"&$B$1&"'!"&C2)
    14
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Scroll Bar and Current Value. Min and Max Values
    By alive555 in forum Excel General
    Replies: 42
    Last Post: 08-16-2015, 10:34 AM
  2. [SOLVED] =indirect( cell ) not working for list
    By Marijn in forum Excel General
    Replies: 13
    Last Post: 08-16-2015, 08:38 AM
  3. [SOLVED] copy and paste in current active cell, and need current date then scroll down 140 lines
    By vengatvj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2013, 03:40 AM
  4. [SOLVED] Find last used cell in specific range (current row) not working with xlToRight Parameter
    By mullock in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-12-2013, 02:49 PM
  5. INDIRECT function is not working when a cell value has Paranthesis()
    By rakisa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-09-2012, 03:04 PM
  6. Working out hours between date in a cell and the current time
    By MasonSinclair in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-19-2011, 06:06 AM
  7. HOW DO I REVERT SCROLL BOX TO CURRENT VISIBLE WINDOW?
    By bak in forum Excel General
    Replies: 1
    Last Post: 04-18-2006, 11:40 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1