Hi
I have two worksheet as follows
1) Summary sheet 2 ) Expense sheet
Summary sheet
Cell B2 is a drop down list of items
Cell E2 drop down list of years ("yearly,2008,2009,2010)
Column B14 to B20 list of items ( same as b2 but not a drop down list)
Expense List
Column A 2 to A9 is a list of items same a B2 and B14 to B20
Row 2 C2 to Y2 is for dates in the format 1-04-20
row 3 c3 to Y3 contains values shown a negative to show as expense
I am trying to set a formula whereby based on cell B2 and E2 in the summary sheet relevent data would be copies to Cell ad 14 from the respective cell in the expense sheet
eg it b2 ="all" and E 2 is yearly the AD14=0,if B2 is="selfi",e2 ="yearly" then AD14 =0,if b2 =Selfi and e2 = 2010 the ad14 will show value of e3 from the expense sheet Clumn
Summary sheet column c and d are totals only . If e 2 is a specific year then AD 14 is apportioned for each month but if E 2 is yearly then the year total shown in the expense column will be shown in row row 14 and ad 14 will be blank/
Summary sheet
I have two worksheet as follows
1) Summary sheet 2 ) Expense sheet
Summary sheet
Cell B2 is a drop down list of items
Cell E2 drop down list of years ("yearly,2008,2009,2010)
Column B14 to B20 list of items ( same as b2 but not a drop down list)
Expense List
Column A 2 to A9 is a list of items same a B2 and B14 to B20
Row 2 C2 to Y2 is for dates in the format 1-04-20
row 3 c3 to Y3 contains values shown a negative to show as expense
I am trying to set a formula whereby based on cell B2 and E2 in the summary sheet relevent data would be copies to Cell ad 14 from the respective cell in the expense sheet
eg it b2 ="all" and E 2 is yearly the AD14=0,if B2 is="selfi",e2 ="yearly" then AD14 =0,if b2 =Selfi and e2 = 2010 the ad14 will show value of e3 from the expense sheet Clumn
Summary sheet column c and d are totals only . If e 2 is a specific year then AD 14 is apportioned for each month but if E 2 is yearly then the year total shown in the expense column will be shown in row row 14 and ad 14 will be blank/
Summary sheet
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
I had tried to set up this formula which does not work any help provided would be appreicated . At the moment i am using nested if function in cell ad 14 IFERROR(IF(SUM(COUNTIF($B$2,{"all","selfi"})),IF($E$2<>"Yearly","",IF($E$2="yearly",0,INDEX(Expense!C3:Expense!Y3,(MATCH($E$2,INDEX(ROW($2009:$2025),0),0)*12+4)))),""),0) |