Indirect - Referencing tab

Cooki

New Member
Joined
Jul 31, 2018
Messages
30
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 :)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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))),"")
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

Cooki

New Member
Joined
Jul 31, 2018
Messages
30
Yes........ thats works perfectly.

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Why use two INDIRECT() when only one is required?

=INDIRECT("'"&B4&"'!G$1")
 
Last edited:

Cooki

New Member
Joined
Jul 31, 2018
Messages
30
This is the formula i was referring too, from Special-K99

Thank you for the post tho, all help greatly received
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,825
Members
406,501
Latest member
TheoDoc

This Week's Hot Topics

Top