Indirect - Referencing tab

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
86
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 :)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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))),"")
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
Yes........ thats works perfectly.

Thank you so much, been at it for couple days now.
 
Upvote 0
Why use two INDIRECT() when only one is required?

=INDIRECT("'"&B4&"'!G$1")
 
Last edited:
Upvote 0
This is the formula i was referring too, from Special-K99

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

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top