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

1. ## 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. ## 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. ## Re: Indirect - Referencing tab

That does not work

4. ## 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))),"")

5. ## Re: Indirect - Referencing tab

Hi
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))),"")`

6. ## Re: Indirect - Referencing tab

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

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

7. ## Re: Indirect - Referencing tab

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

=INDIRECT("'"&B4&"'!G\$1")

Be good

9. ## 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

10. ## Re: Indirect - Referencing tab

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

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

## User Tag List

#### Posting Permissions

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