Good afternoon!
I've been searching for several hours but can't seem to find the exact answer that will work for me. I have a workbook that contains yearly forecasts for various categories of products -- each is contained in a separate tab (22 worksheets in total). All worksheets are structured in the same fashion - data ranges have been made the same.
I have a summary tab where I would like to pull in these final forecasts by product number and year.
I've been able to pull in the results if I know what sheet the data is on via the following:
=INDEX(Toys!$Y$5:$AE$150,MATCH($A24,Toys!$A$5:$A$150,0),MATCH(H$5,Toys!$Y$2:$AE$2,0))
What I would like to do is have the formula look through the tabs so I do not need to specify where to look for each product. I think this means I need to use the INDIRECT function. I've created a list of the 22 worksheet names in in AZ2:AZ23 of the summary tab. I've tried various versions of the below but can't seem to get it too work.
=INDEX(INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$Y$5:$AE$150"),MATCH($A24,INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$A$5:$A$150"),0),MATCH(H$5,INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$Y$2:$AE$2"),0))
Any thoughts on what I'm missing or is there a simpler way?
Thanks in advance for any advice!
Bookmarks