+ Reply to Thread
Results 1 to 10 of 10

Using INDIRECT function in multiple rows.

  1. #1
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Using INDIRECT function in multiple rows.

    I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.

    My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.

    At first I tried using absolutes ($) to fix the problem, but that's a different ballgame.
    I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.

    But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.

  2. #2
    Registered User
    Join Date
    03-22-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2010
    Posts
    39

    Re: Using INDIRECT function in multiple rows.

    hi JeffreyNile,

    Attach your sheet so I can address you correctly.

    As i understood here, you don't want to re-type the indirect function?

    regards

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Using INDIRECT function in multiple rows.

    If your list of names is unique, you could do something like this...

    A
    B
    C
    1
    Name
    2
    aa aa
    3
    bb bb
    4
    cc cc
    5
    dd dd
    6
    ee ee
    7
    ff ff
    8
    gg gg
    9
    0
    10
    11
    12


    C2=IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$10),0,0),0)),"")

    You could move that to the sheet you need it on.

    Or if you just want to pull the data in as it is...
    =IF(INDEX(Sheet4!A:A,ROW(A2))="","",INDEX(Sheet4!A:A,ROW(A2)))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using INDIRECT function in multiple rows.

    Worked like a charm. I made the proper substitutions:

    =IF(INDEX('DIRECTORY 2014'!B:B,ROW(C5))="","",INDEX('DIRECTORY 2014'!B:B,ROW(C5)))

    Guess my function skills have a lot more to improve on.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Using INDIRECT function in multiple rows.

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

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

    Re: Using INDIRECT function in multiple rows.

    Hi there,

    works also with INDIRECT:

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

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

  7. #7
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using INDIRECT function in multiple rows.

    Will do. This is my first post. Where do I mark solved?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Using INDIRECT function in multiple rows.

    Miros, nice formula

    Note though that INDIRECT() is a volatile function (it updates with every workbook change), and if used in significant numbers will tend to slow things down. If there are only a few, it's not a problem though

    see this link for more info on voliatile functions
    http://www.decisionmodels.com/calcsecretsi.htm

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Using INDIRECT function in multiple rows.

    2. If your question is resolved, mark it SOLVED using the thread tools just above your 1st post

  10. #10
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using INDIRECT function in multiple rows.

    Perfect. I read right over that one. Thanks for your help once again.

+ 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. Using Indirect function to sum across multiple sheets
    By Irishman07 in forum Excel General
    Replies: 10
    Last Post: 02-14-2018, 05:26 PM
  2. Replies: 8
    Last Post: 07-13-2012, 06:48 AM
  3. Replies: 7
    Last Post: 03-09-2012, 01:29 AM
  4. Moving Down Rows using indirect and address function
    By plitv001 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-02-2008, 01:16 PM
  5. [SOLVED] Sum Indirect function through multiple sheets
    By Andre Croteau in forum Excel General
    Replies: 2
    Last Post: 05-06-2005, 06:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1