building code for an individual report

[alymcmorland]

Hi,

I need help, i have built an attendance database in excel, this is
basicly like a register where a user can iput everyones status for that
day, whether it be in, late, sick, on holiday or doing overtime.
Everyone who is on this database has their name at the top, so the
staff names run horizontally while the date runs down the left most
column. i have a screen shot of what this attendance database looks
like.

So now this has been completed i need to built reporting tools. I've
already built two or three, but now i must build an individual
reporting tool where it reports on an individual, basicly bringing up
their details (name, department, ins, lates, sick, sick percentage,
holiday, overtime) after their name has been put in an input box.

What i need help on is if anyone has any idea where to start in an
attempt to complete this tool. I'm absolutely crap when it comes to
VBA, but i know the basics.

The way i want it to work is the user clicks a button which brings up
an input box, then the user puts the name of whoever they want to
report on in, this will look at the name the user has put in an match
it with a name on the table and then put in the correct numbers for ins
lates and so on.

There is also a twist, not only does the report have to do what i said
above, but when it becomes 2006 the report must report on 2006 instead
of 2005, so this means there has to be something that works out the
year so that it can report from the right tab sheet. Each of the tab
sheets are named by year so from 2005 to 2010, each tab sheet is
exactly the same except obv weekends are on different days, but apart
from that they're pretty much the same.

I also have a screen shot of the individual report after the user has
clicked on the change data button which brings up the input window to
input whichever staff member it is that the user would like to report
on.

If anyone could push me in the right direction or give me some code
that would be great, also if anyone needs to see any other screenshots
to complete this just say.

Thanks!


+-------------------------------------------------------------------+
|Filename: maintabsheet2.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=3999 |
+-------------------------------------------------------------------+

 

[damorrison]

you could start by checking out the example

www.j-walk.com/ss/excel/usertips/tip020.htm
hopefuly this will get you started

 

[alymcmorland]

In one of my formula's i have

'2005'!B5

This is pointed at tab sheet 2005, what i'd like to know is if it is
possible to have this in there instead:

'Current Year'!B5

and basicly translate the current year to tab sheet name so that the
refferenced tab sheet will change from year to year without anyone
having to change it?

 

[damorrison]

could you not activate the sheet in the open event?

 

[Rowan Drummond]

You could use the indirect function eg:
=INDIRECT(YEAR(TODAY())&"!B5")

Note: this is a volatile function and will be recalculated everytime you
open, close, save or perform any other action on the workbook that
triggers calculation.

Hope this helps
Rowan