I'm working on a quarterly Board report which i'm trying to set up so that you don't have to amend any of the data when you open it.
Currently there is a month-by-month break down of all the figures in one worksheet, then the report itself is on a different worksheet. Is there a formula that would pull the most recent months data through to the report worksheet?
The data I'm trying to pull through is "Live Vacancies".
So for example the monthly data would be:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Live Vacancies[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want a cell in the report worksheet which would pull through the most recent value depending on when the report is being used. So if it is opened in May then the number of live vacancies in the report would come through as 3 and so on for whichever month the report is opened.
I would assume you could use the =today() function then change the format of the cell to "mmm" and use the =IF function in conjunction somehow?
Any help would be great!
Ta
Currently there is a month-by-month break down of all the figures in one worksheet, then the report itself is on a different worksheet. Is there a formula that would pull the most recent months data through to the report worksheet?
The data I'm trying to pull through is "Live Vacancies".
So for example the monthly data would be:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Live Vacancies[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want a cell in the report worksheet which would pull through the most recent value depending on when the report is being used. So if it is opened in May then the number of live vacancies in the report would come through as 3 and so on for whichever month the report is opened.
I would assume you could use the =today() function then change the format of the cell to "mmm" and use the =IF function in conjunction somehow?
Any help would be great!
Ta