Hi
I have data for every month of the year arranged in columns, so months are cells G27-R27, and projects are listed E28-E46. Budgeted Expenditure on each project is then filled in for each project each month in the grid G28-R46.
I have a separate reporting file, and I need to return a figure for the total that was budgeted for each individual project so far in the year. In my reporting file I have the month available (1-12 in cell C7).
So far I've tried:
CHOOSE
=SUM('Link'!$G28:CHOOSE($C$7,'Link'!$G28,'Link'!$H28,'Link'!$I28,'Link'!$J28,'Link'!$K28,'Link'!$L28,'Link'!$M28,'Link'!$N28,'Link'!$O28,'Link'!$P28,'Link'!$Q28,'Link'!$R28))
and OFFSET
=SUM(OFFSET('Link'!$G28,0,0,1,$C$7))
Both of these work fine until I close the reference file, then I get errors. I can't 'update links' without the reference file open, and after some research I believe this is just fundamentally how these formulas work.
Is there a formula that I can use that would still work with the workbook closed? I really don't want to have to change anything or update anything in the reference file if I don't have to. I also don't want to just copy across all the data and perform the calculation locally, as the data set is actually quite a bit bigger and more complicated than my example.
Thank you for your help
I have data for every month of the year arranged in columns, so months are cells G27-R27, and projects are listed E28-E46. Budgeted Expenditure on each project is then filled in for each project each month in the grid G28-R46.
I have a separate reporting file, and I need to return a figure for the total that was budgeted for each individual project so far in the year. In my reporting file I have the month available (1-12 in cell C7).
So far I've tried:
CHOOSE
=SUM('Link'!$G28:CHOOSE($C$7,'Link'!$G28,'Link'!$H28,'Link'!$I28,'Link'!$J28,'Link'!$K28,'Link'!$L28,'Link'!$M28,'Link'!$N28,'Link'!$O28,'Link'!$P28,'Link'!$Q28,'Link'!$R28))
and OFFSET
=SUM(OFFSET('Link'!$G28,0,0,1,$C$7))
Both of these work fine until I close the reference file, then I get errors. I can't 'update links' without the reference file open, and after some research I believe this is just fundamentally how these formulas work.
Is there a formula that I can use that would still work with the workbook closed? I really don't want to have to change anything or update anything in the reference file if I don't have to. I also don't want to just copy across all the data and perform the calculation locally, as the data set is actually quite a bit bigger and more complicated than my example.
Thank you for your help
