hi,

I have a workbook containing a worksheet called Scorecard which is actually
a performance score (weightage) for agents for a particular month.

i have entered formulas & named ranges by which the formulas extract data
using VLOOKUP & named ranges from sheet2 & there is a 3rd sheet sheet3
called Key which gives the weightage for agents performance falling in
between a particular level.

like agent promises taken score is between 300 to 350, weightage will be 8
351 to
400, weightage will be 10
& so on & on.

the above is just to give an overview of what i have made.

There is a data validation listbox (using Data menu Validation, select
List, give a name range e.g. =months)
what i want to do is selecting the month from the data validation list e.g.
April, the "april" sheet should get activated & the defined ranges will get
updated for that sheet & automatically the formulas on scorecard will show
the correct data for April month. similarly, if May is selected from list,
May sheet will get activated & named ranges will update themselves with the
ranges in may sheet & eventually Scorecard sheet will reflect the data for
may.

Can anybody help me with this?

Eijaz

I've rather lost the plot with some of your detail below, but if your data
validation cell (displaying the months) is in Cell E1, then in the Worksheet
Module (right-click the tab & select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
Sheets(Target.Value).Activate
End If
End Sub

Note the Data Validation Values must ve spelt exactly as the Tab (Sheet)
names.

In each monthly sheet module, use the Activate event to fire the update of
your ranges (that's the bit I can't quite get to grips with - sorry........)


--
HTH
Roger
Shaftesbury (UK)



"gr8guy" wrote in message
...
hi,

I have a workbook containing a worksheet called Scorecard which is
actually
a performance score (weightage) for agents for a particular month.

i have entered formulas & named ranges by which the formulas extract data
using VLOOKUP & named ranges from sheet2 & there is a 3rd sheet sheet3
called Key which gives the weightage for agents performance falling in
between a particular level.

like agent promises taken score is between 300 to 350, weightage will be 8
351 to
400, weightage will be 10
& so on & on.

the above is just to give an overview of what i have made.

There is a data validation listbox (using Data menu Validation, select
List, give a name range e.g. =months)
what i want to do is selecting the month from the data validation list
e.g.
April, the "april" sheet should get activated & the defined ranges will
get
updated for that sheet & automatically the formulas on scorecard will show
the correct data for April month. similarly, if May is selected from list,
May sheet will get activated & named ranges will update themselves with
the
ranges in may sheet & eventually Scorecard sheet will reflect the data for
may.

Can anybody help me with this?

Eijaz

hi,

i know that i was too much in a hurry to send a post.

Firstly, thanks for the idea! i am sure thats what i was missing. i have to think about the next part of code to complete my workbook.

i'll try the best to explain what i am trying to do.

There is a main worksheet called "Scorecard", with a validation list on cell say, E3 showing list of months, & this Sheet will reflect the monthly data (from selected monthly sheet) & give a sort of rating (weightage) using a "Key" worksheet for performance ratings, for every agent in my team.
There would be month sheets for the 12 months namely say, Jan-2004, Feb-04,March-04...etc.

on the month sheets( all Similar in format & Range) , i have the raw data (for that month) in tabular form, & i have given names for each range, say different teams names under which are different agents:

e.g:
Team-name promises_Taken Promises_Kept Wrap% Schedule Adherence Attendance
================================================== ===============================
Team Rhys =average(a1:a10) =average(b1:b10) =average(c1:c10) =average(d1:d10) =average(e1:e10)
agent_ram
agent_rahim
agent_charles THIS WOULD BE RANGE1
agent_sobraj
agent_james
agent_carter
etc..................

Team Monisha =average(a1:a10) =average(b1:b10) =average(c1:c10) =average(d1:d10) =average(e1:e10)
agent_meera
agent_mecwan THIS WOULD BE RANGE2
agent_lorraine
etc......

now, these named ranges will be in all monthly sheets, such that when i select a particular month (say May-04) from the validation dropdown list, the particular month sheet will be activated & the named ranges, viz Range1 & Range2 which were referencing data from say April-04 sheet will now reference data from May-04 sheet. so the new named ranges will reflect the may data instead of the April data & that will reflect in Scorecard sheet through Vlookup formula & the referencing of the named ranges in VLookup.


I dont know how to change the named ranges through VBA so that whenever a month sheet is selected, the Range will change & reflect for that month sheet. e.g.

Range1 = April-04!$A$1:$E$10 will change to Range1=May-04!$A$1:$E$10
Range2 = April-04!$A$12:$E$20 will change to Range1=May-04!$A$12:$E$20


Rgds,

Eijaz





================================================== ====================================


"Roger Whitehead" wrote in message ...
I've rather lost the plot with some of your detail below, but if your data
validation cell (displaying the months) is in Cell E1, then in the Worksheet
Module (right-click the tab & select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
Sheets(Target.Value).Activate
End If
End Sub

Note the Data Validation Values must ve spelt exactly as the Tab (Sheet)
names.

In each monthly sheet module, use the Activate event to fire the update of
your ranges (that's the bit I can't quite get to grips with - sorry........)


--
HTH
Roger
Shaftesbury (UK)



"gr8guy" wrote in message
...
hi,

I have a workbook containing a worksheet called Scorecard which is
actually
a performance score (weightage) for agents for a particular month.

i have entered formulas & named ranges by which the formulas extract data
using VLOOKUP & named ranges from sheet2 & there is a 3rd sheet sheet3
called Key which gives the weightage for agents performance falling in
between a particular level.

like agent promises taken score is between 300 to 350, weightage will be 8
351 to
400, weightage will be 10
& so on & on.

the above is just to give an overview of what i have made.

There is a data validation listbox (using Data menu Validation, select
List, give a name range e.g. =months)
what i want to do is selecting the month from the data validation list
e.g.
April, the "april" sheet should get activated & the defined ranges will
get
updated for that sheet & automatically the formulas on scorecard will show
the correct data for April month. similarly, if May is selected from list,
May sheet will get activated & named ranges will update themselves with
the
ranges in may sheet & eventually Scorecard sheet will reflect the data for
may.

Can anybody help me with this?

Eijaz