I'm trying to combine a table of project revenues (from multiple workbooks) for planning with a dynamic control to delay or pull forward (Offset) a project.
The various project workbooks have varying start dates and formats therefore I am using index/match too ensure the matching of the right months (col) and revenue (Row) and for ease in updating in the coming months.
The index match works great until I try to move the projects around by adding or subtracting a number from the col field in the index formula. This works some what (95% correct)
eg. Delay project by 6 months so -6 from the column match to push that project out.
Where I have the issues is where the Col reference comes to Col 0 eg Col Match = June (col 6) less 6 for my offset = Index(Array(xx:XX),Row(0),Col(6)-6)
or =INDEX(Testdata1,MATCH($I72,TestDescription1,0),(MATCH(K$69,TestTimeline1,0)-$J$59))
J59 = offset eg. 6 months/"6"
The answer should error out (Which I want too for an iferror) which is does if I put a 0 however when I use range names in the index match it pulls in another number from within the data set eg col 6 from with in the data set.
Hope I have explained this well enough, no idea how else to get around this at the moment.
The various project workbooks have varying start dates and formats therefore I am using index/match too ensure the matching of the right months (col) and revenue (Row) and for ease in updating in the coming months.
The index match works great until I try to move the projects around by adding or subtracting a number from the col field in the index formula. This works some what (95% correct)
eg. Delay project by 6 months so -6 from the column match to push that project out.
Where I have the issues is where the Col reference comes to Col 0 eg Col Match = June (col 6) less 6 for my offset = Index(Array(xx:XX),Row(0),Col(6)-6)
or =INDEX(Testdata1,MATCH($I72,TestDescription1,0),(MATCH(K$69,TestTimeline1,0)-$J$59))
J59 = offset eg. 6 months/"6"
The answer should error out (Which I want too for an iferror) which is does if I put a 0 however when I use range names in the index match it pulls in another number from within the data set eg col 6 from with in the data set.
Hope I have explained this well enough, no idea how else to get around this at the moment.
