Choosing different cell values by user clicking a button

Hi,

Right i have a worbook that has 6 tab sheets, these are named:

2005
2006
2007
2008
2009
2010

On each tab sheet is an attendance register where a user can fill in which staff are absent, which are in and late and so on and so forth.

i have a 7th tab sheet, on this tab sheet is a report which counts the amount of absences, in's, lates per staff member from the other 6 sheets. There are also 6 radio buttons,

2005
2006
2007
2008
2009
2010

when a user clicks on one of these radio buttons it changes the values in the report to report on that year.

Seeing as the report uses the same code for each year (the only thing that differs from code to code is the tab sheet of which the COUNTIF statements refference) i was wandering is there a way to set the year to say X and make X a variable and then depending on which radio button is selected would depend on which value X would be:

2005, 2006, 2007, 2008, 2009 or 2010

So for example for one report the COUNTIF statement looks like this:

Please Login or Register  to view this content.

and then for the 2006 tab it would look like:

Please Login or Register  to view this content.

so they are the same apart from the refferenced tab sheet of which is the year. So instead of writing out this code 6 times over is there not a way to write it out once with a variable instead of the tab sheet and then depending on which radio button is pushed depends on what the variable is set to?

If anyone could provide any code it would be extremely helpful. Thanks!

sStr "=(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "IN" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

Range("B5").Formula = Replace(sStr,"ZZZZ","2005")

--
Regards,
Tom Ogilvy



"alymcmorland" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> Right i have a worbook that has 6 tab sheets, these are named:
>
> 2005
> 2006
> 2007
> 2008
> 2009
> 2010
>
> On each tab sheet is an attendance register where a user can fill in
> which staff are absent, which are in and late and so on and so forth.
>
> i have a 7th tab sheet, on this tab sheet is a report which counts the
> amount of absences, in's, lates per staff member from the other 6
> sheets. There are also 6 radio buttons,
>
> 2005
> 2006
> 2007
> 2008
> 2009
> 2010
>
> when a user clicks on one of these radio buttons it changes the values
> in the report to report on that year.
>
> Seeing as the report uses the same code for each year (the only thing
> that differs from code to code is the tab sheet of which the COUNTIF
> statements refference) i was wandering is there a way to set the year
> to say X and make X a variable and then depending on which radio button
> is selected would depend on which value X would be:
>
> 2005, 2006, 2007, 2008, 2009 or 2010
>
> So for example for one report the COUNTIF statement looks like this:
>
>
> Code:
> --------------------
>
> Range("B5").Formula = "=(COUNTIF('2005'!D6:D370," & Chr(34) & "IN" &
Chr(34) & ")) + (COUNTIF('2005'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('2005'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('2005'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"
>
> --------------------
>
>
> and then for the 2006 tab it would look like:
>
>
> Code:
> --------------------
>
> Range("B5").Formula = "=(COUNTIF('2006'!D6:D370," & Chr(34) & "IN" &
Chr(34) & ")) + (COUNTIF('2006'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('2006'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('2006'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"
>
> --------------------
>
>
> so they are the same apart from the refferenced tab sheet of which is
> the year. So instead of writing out this code 6 times over is there
> not a way to write it out once with a variable instead of the tab sheet
> and then depending on which radio button is pushed depends on what the
> variable is set to?
>
> If anyone could provide any code it would be extremely helpful.
> Thanks!
>
>
> --
> alymcmorland
> ------------------------------------------------------------------------
> alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
> View this thread: http://www.excelforum.com/showthread...hreadid=480792
>

Hi, if i want to choose a tab sheet by the date would i put something like:

Please Login or Register  to view this content.

I know thats wrong above, could anyone correct my mistake(s)?!

You have to concatenate

Sub Macro_DropRef()
z = Date
na = "DropRef " & z
Sheets(na).Select
End Sub

Ole Michelsen

"alymcmorland" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi, if i want to choose a tab sheet by the date would i put something
> like:
>
>
> Code:
> --------------------
>
> Sub Macro_DropRef()
>
> Get Date
> zzzz = Date
> Sheets("DropRefzzzz").Select
>
> End Sub
>
> --------------------
>
>
> I know thats wrong above, could anyone correct my mistake(s)?!
>
>
> --
> alymcmorland
> ------------------------------------------------------------------------
> alymcmorland's Profile:
> http://www.excelforum.com/member.php...o&userid=27652
> View this thread: http://www.excelforum.com/showthread...hreadid=480792
>

That returned the error of subscript out of range, and when i debugged it the highlighted line is:

Sheets(na).Select

Can you help with checking that error or maybe giving some idea as to why it would say that?