My colleague has created a table in excel to pull out figures from an excel
spreadsheet, to summarise the figures contained within it and to
cross-reference/check that the figures entered are correct. She does this on
the 22nd of each month. The table shows dates along the top (the 22 of each
month), a list of 6 company names in column A (starting at A2) and the rest
of the cells showing an amount.
Is there any way of automating this process? Perhaps a macro/button, so on
the 22nd she can press this button to extract the cell values needed.
I had considered a simple formula to say: =IF(Today()=H4,B1,B2)
(where H4 contains the date 22/05/2007). Doing it this way though, when the
date does not equal H4, the cells will show nothing?
Any ideas?

I'm not sure if this will fully meet the need or not. But she could try
recording a macro to do the process once and then simply call up that macro
when she needs to repeat the process on the 22nd of a month.

Tools | Macro | Record New Macro
and make sure that the place to put it is in 'This Workbook' so it stays
with the workbook. She'll even be given the opportunity to give the macro a
name like MonthlyUpdate rather than just Macro1.

If the process is exactly the same each month - going to the same file,
sheet, group of cells, to get the information (cut & paste I presume?) then
this will work. But if there is any variation in the process at all, it'll
probably not give the expected results. Recorded macros record exactly what
you do and repeat it faithfully, blindly and stupidly every time you call on
it again later. You can tailor such a macro to become less-stupid, but that
requires at least some knowledge of VBA programming. If you've got that,
you're good to go. If not, perhaps once you've got the basic macro recorded
and can explain what parts need to be more robust, people here could help
'tweak' it and educate it some.

"Sarah (OGI)" wrote:

My colleague has created a table in excel to pull out figures from an excel
spreadsheet, to summarise the figures contained within it and to
cross-reference/check that the figures entered are correct. She does this on
the 22nd of each month. The table shows dates along the top (the 22 of each
month), a list of 6 company names in column A (starting at A2) and the rest
of the cells showing an amount.
Is there any way of automating this process? Perhaps a macro/button, so on
the 22nd she can press this button to extract the cell values needed.
I had considered a simple formula to say: =IF(Today()=H4,B1,B2)
(where H4 contains the date 22/05/2007). Doing it this way though, when the
date does not equal H4, the cells will show nothing?
Any ideas?