yogi_LookUp Value In Cell A1 Of Sequential Sheet Number Listed In Cells A3:A

Google Spreadsheet   Post  #2410

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-01-2018

question by: Ed100-Jeff
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Bv9K27FjHxg;context-place=forum/docs

Using INDIRECT() to refer to a newly added sheet in a workbook ($10 bounty for solution!)
Issue: I'm struggling to get the INDIRECT() function to work across sheets when new sheets are added to a workbook.

Simplified example of the challenge: How can I make the first sheet of this workbook calculate when adding new sheets???:

https://docs.google.com/spreadsheets/d/1rLvD_hyZ--as75Ap1b3Ip2AT6RLnKwLZLG8HGsYvvgc/edit#gid=0 

[  Full case: The sheet I'm building is for a nonprofit blog (Ed100.org) -- here is the work in progress: 

Public workbook link: https://docs.google.com/spreadsheets/d/1IFZ4QyEW7Bwy3_QIs_D0dExpid__yGLp3HMIVD3Y-Yg/edit?usp=sharing

To replicate the problem: 

• Duplicate the sheet named "Raven"
• Rename the duplicate sheet "Test"
• Activate the sheet named "School List"
• In Cell B17 enter the value "Test"
• Activate the sheet "Decision Matrix"

The expected result is for data indirectly referenced from the new "Test" sheet to be reflected in the "Decision Matrix" sheet in cell F17. But it doesn't work reliably. I find that I have to mess with refreshing different cells until I get lucky and trigger a recalc that causes the "Decision Matrix" sheet to "see" the new sheet. 

What am I missing?