Variable Sheet name but relative cell

[Cheryl4g]

I have been stuck on this problem for a while now and can't figure out a way to solve it and desperately need some help from the group.

I don't have permission to post an attachement of my file so I will try to explain my issue as best I can. I have posed the question before but only asked about the indirect solution and now I'm hoping for some other ideas since I can't get that to work.

I have a workbook with the following tabs
Summary
2011
2012
2013
2014
Forecast Collection 2011
Forecast Collection 2012
Forecast Collection 2013
Forecast Collection 2014

For example the data entered on tab "2011" will be pulled into tab "Forecast Collection 2011" which is then pulled into the Summary tab.

I currently have formulas in all of the sheets to update the years in all of the formulas when the current year changes from 2010 to 2011, etc. on the summary tab and a macro that changes the sheet names to the correct new years.

My problem is how do I update the following formula to pull data from the correct sheet and keep the formula relative so that if a line is added in sheet 2011, then sheet Forecast 2011's formulas will adjust to that new address.

Original formula:
=IF(C$10>Summary!$AE$8,'2011'!C47,0)

This also worked
=IF(C$10>Summary!$AE$8,(INDIRECT("'"&$A$1&"'!C47")),0)
Except for when a new line is added to sheet 2011. then all of the formulas get screwed up.

what's the best way to pull the data into the speadsheet with a cell address that has a sheet name that can change but can't use abosolute references?
I also tried maybe using index and Match but I can't get the syntax right.

Help..... I am losing my mind.

 

[Ganjin]

hmm, so what identifies C47 or (if you add a line) D47 as the Cell you want the formula to consider?

 

[Cheryl4g]

oops that would have been helpful.

The totals for the groups- row
total labor
total materials
total hardware
total software
etc
is pulled in by month
column headers are months 1- 12

That's why I thought maybe index match would work

 

[Ganjin]

hmm, would this example be of some help?

Sheet1:
<table border="0" cellpadding="0" cellspacing="0" width="66"><col width="66"><tbody><tr height="20"> <td class="xl65" style="height: 15pt; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(220, 230, 241);" width="66" height="20">5</td> </tr></tbody></table>Formula:
=INDEX(INDIRECT("'"&"Sheet2"&"'!A:L"),MATCH("Oranges",INDIRECT("'"&"Sheet2"&"'!A:A"),0),MATCH("February",INDIRECT("'"&"Sheet2"&"'!1:1"),0))

INDEX(Sheet2!A:L,MATCH("Oranges",Sheet2!A:A,0),MATCH("February",Sheet2!1:1,0))
​

Sheet2:
<table border="0" cellpadding="0" cellspacing="0" width="308"><col style="width: 58pt;" width="77" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 58pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);" width="77" height="20">
</td> <td class="xl65" style="width: 58pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);" width="77">January</td> <td class="xl65" style="width: 58pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);" width="77">February</td> <td class="xl67" style="width: 58pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);" width="77">March</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" height="20">Apples</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> <td class="xl67" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);" height="20">Oranges</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);">
</td> <td class="xl65" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);">5</td> <td class="xl67" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217);">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" height="20">Grapes</td> <td class="xl69" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> <td class="xl69" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> <td class="xl70" style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;">
</td> </tr> </tbody></table>

 

[Cheryl4g]

So I adjusted the formula for my data and I get a #N/A
Can you see where I'm goofing it up?

=INDEX(INDIRECT("'"&$A$1&"'!A:P"),MATCH("Total Material",INDIRECT("'"&$A$1&"'!A:A"),0),MATCH("1",INDIRECT("'"&$A$1&"'!10:10"),0))

$A$1 is the cell that contains the year, for example "2012"
Row 10 has the headers for months 1-12
Col A has the subtotals for Materials, software, etc.

 

[Ganjin]

=INDEX(INDIRECT("'"&$A$1&"'!A:P"),MATCH("Total Material",INDIRECT("'"&$A$1&"'!A:A"),0),MATCH(1,INDIRECT("'"&$A$1&"'!10:10"),0))

 

[Cheryl4g]

Thank You, so much it finally works!!!!!!! Yeah!!!!
I feel my sanity slowing returning.

thank you,