Indirect - Referencing tab
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Indirect - Referencing tab
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Indirect - Referencing tab

    Hi i am working on on a training matrix at work, there are 8 members of staff with a tab named after them.

    There are 58 odd tasks they need training on and have a measurement of out of 5 on the progress, if there progress is 3 or blow they still need training on that task.

    i have made a training page that automatically updates with the tasks they still need training on - Formula is

    =IFERROR(INDEX('Bobby Bland'!$B$1:$B$58,AGGREGATE(15,6,ROW('Bobby Bland'!$C$1:$C$58)/('Bobby Bland'!$C$1:$C$58<3),ROW(5:5))),"")

    on the training page i just want to use a drop down menu in B4 where i can select any of the 8 members of staff, then the tasks update on that page with that members of staff training tasks.

    Ive used indirect in the past to reference tab names based on a cell

    Ive tried using indirect in the above formula but cant get it to work, formula

    =IFERROR(INDEX("'"&$B$4&"'!"&"$B$1:$B$58",AGGREGATE(15,6,ROW("'"&$B$4&"'!"&"$C$1:$C$58)/("'"&$B$4&"'!"&"$C$1:$C$58<3),ROW(5:5))),"")

    Hope that all makes sence

    praying someone can help

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Indirect - Referencing tab

    Try

    =IFERROR(INDEX(INDIRECT("'"&$B$4&"'!$B$1:$B$58"),AGGREGATE(15,6,ROW(INDIRECT("'"&$B$4&"'!$C$1:$C$58""))/(INDIRECT("'"&$B$4&"'!$C$1:$C$58")<3),ROW(5:5))),"")

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    That does not work

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Indirect - Referencing tab

    Too many double quotes, try

    =IFERROR(INDEX(INDIRECT("'"&$B$4&"'!$B$1:$B$58"),AGGREGATE(15,6,ROW(INDIRECT("'"&$B$4&"'!$C$1:$C$58"))/(INDIRECT("'"&$B$4&"'!$C$1:$C$58")<3),ROW(5:5))),"")
    Last edited by Special-K99; Aug 22nd, 2019 at 05:02 AM.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    147
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    Hi
    well, I guess this sample might help you to build on
    Code:
    =INDIRECT("'"&INDIRECT("b4")&"'!"&"g$1")
    Then may be
    Code:
    =IFERROR(INDEX(INDIRECT(("'"&INDIRECT("b4")&"'!"$B$1:$B$58"),AGGREGATE(15,6,ROW(INDIRECT(("'"&INDIRECT("b4")&"'!"$C$1:$C$58"))/(INDIRECT("'"&$B$4&"'!$C$1:$C$58")<3),ROW(5:5))),"")
    Last edited by mohadin; Aug 22nd, 2019 at 05:24 AM.

  6. #6
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    Yes........ thats works perfectly.

    Thank you so much, been at it for couple days now.

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Indirect - Referencing tab

    Why use two INDIRECT() when only one is required?

    =INDIRECT("'"&B4&"'!G$1")
    Last edited by Special-K99; Aug 22nd, 2019 at 05:26 AM.

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    147
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    Glad It helps
    Be good

  9. #9
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    This is the formula i was referring too, from Special-K99

    Thank you for the post tho, all help greatly received
    Last edited by Cooki; Aug 22nd, 2019 at 05:38 AM.

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    147
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect - Referencing tab

    Quote Originally Posted by Special-K99 View Post
    Why use two INDIRECT() when only one is required?

    =INDIRECT("'"&B4&"'!G$1")
    You are correct
    cheers

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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