Indirect - Referencing tab

[Cooki]

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

 

[Special-K99]

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

 

[Cooki]

That does not work

 

[Special-K99]

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

 

[mohadin]

Hi
well, I guess this sample might help you to build on

=INDIRECT("'"&INDIRECT("b4")&"'!"&"g$1")

Then may be

=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))),"")

 

[Cooki]

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

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

 

[Special-K99]

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

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

 

[mohadin]

Glad It helps
Be good

 

[Cooki]

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

Thank you for the post tho, all help greatly received

 

[mohadin]

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

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

You are correct
cheers