Named Ranges
 

Hello NG,
I'e been using Excel for a while but I'm new to Named Ranges.
I have a column of hyperlinks, on a master page, to cells I've named on
indiuidual pages.
example on the sheet for Smith in cell A2 I call it Smith_Week_1
and again on Smith in Cell A18 I call it Smith_Week_2.
and so forth down the page for about 20 different cells.
I do this for 9 employees.
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?
Thanks for any help
--
James

Named Ranges
 

Sub ScrollMyCell()
ActiveWindow.ScrollRow = Range("MyCell").Row
ActiveWindow.ScrollColumn = Range("MyCell").Column

End Sub

"James" wrote:

Hello NG,
I'e been using Excel for a while but I'm new to Named Ranges.
I have a column of hyperlinks, on a master page, to cells I've named on
indiuidual pages.
example on the sheet for Smith in cell A2 I call it Smith_Week_1
and again on Smith in Cell A18 I call it Smith_Week_2.
and so forth down the page for about 20 different cells.
I do this for 9 employees.
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?
Thanks for any help
--
James

Named Ranges
 

gocush,
I'm kind of at a loss here, how do I use this with a hyperlink?
I have about 20 cells that have hyperlinks in them, do I write this code for
each one and then put the named range in the ("MyCell") place?
Thanks for your assistance
James

"gocush" wrote:

Sub ScrollMyCell()
ActiveWindow.ScrollRow = Range("MyCell").Row
ActiveWindow.ScrollColumn = Range("MyCell").Column

End Sub

"James" wrote:

Hello NG,
I'e been using Excel for a while but I'm new to Named Ranges.
I have a column of hyperlinks, on a master page, to cells I've named on
indiuidual pages.
example on the sheet for Smith in cell A2 I call it Smith_Week_1
and again on Smith in Cell A18 I call it Smith_Week_2.
and so forth down the page for about 20 different cells.
I do this for 9 employees.
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?
Thanks for any help
--
James

Named Ranges
 

From you OP:
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?

also from you op I gather that you have named cells on a sheet for Smith:
Smith_Week_1 ..... to Smith_Week_2.

First of all these named cells are Named Ranges. A Named Range is simply a
range of cells (one or more) that you have given a name to, either in the
Name box or thru InsertNameDefine

I think what you are trying to achieve is this:
Whenever you activate the Smith sheet, the cell Smith_Week_xxx will appear
in the upperleft corner of the monitor screen to match whatever week it is
today.
If this assumption is correct then
1. You need a way to determine a date when Week_1 begins. Let's say it's
1/1/05

2. On a new Worksheet (call it "K"), you could set up a table and enter
1/1/05 in A1 and 1 in B1. Then fill down in col B 1-20. Enter in cell A2
the formula: =A1+7. Copy this formula down to A20, which will give you the
starting date for 20 weeks.

3. Select this table (A1 to B20) and give it a Name: WeekTable.

3. In C1 enter the formula =VLookup(Today(),WeekTable,2)
Give C1 the name: ThisWeek
(This should result in a number from 1 to 20 depending on which week we
are in.)

4. Rt-Click on the TAB at the bottom of the sheet that says Smith. Then
click on View Code.

5. In the rt panel enter the following

Public Sub Worksheet_Activate
dim i as integer
i = Sheets("K").Range("ThisWeek")
ActiveWindow.ScrollRow = Range("Smith_Week_" & i).Row
ActiveWindow.ScrollColumn = Range("("Smith_Week_" & i").Column
End Sub

This is called a Worksheet_event. It is triggered, or is "fired off" every
time this sheet is activated.
If this works for the Smith sheet the way you want, then you can expand it
in a couple of ways. First, as it is, you will have to manually change the
start date(A1), perhaps quarterly. You can add vba code so start date
changes automatically.
Next you can expand this by assigning the name of the sheet as a variable,
like we did with i (the week number).

You would then use the module in the vb editor called ThisWorkbook and enter
code in the following event:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
.............
End Sub

This works for all sheets in ThisWorkbook. The variable Sh is the sheet
name: Sheet.Name

Work with this for a while and see if it gets you started.

"James" wrote:

gocush,
I'm kind of at a loss here, how do I use this with a hyperlink?
I have about 20 cells that have hyperlinks in them, do I write this code for
each one and then put the named range in the ("MyCell") place?
Thanks for your assistance
James

"gocush" wrote:

Sub ScrollMyCell()
ActiveWindow.ScrollRow = Range("MyCell").Row
ActiveWindow.ScrollColumn = Range("MyCell").Column

End Sub

"James" wrote:

Hello NG,
I'e been using Excel for a while but I'm new to Named Ranges.
I have a column of hyperlinks, on a master page, to cells I've named on
indiuidual pages.
example on the sheet for Smith in cell A2 I call it Smith_Week_1
and again on Smith in Cell A18 I call it Smith_Week_2.
and so forth down the page for about 20 different cells.
I do this for 9 employees.
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?
Thanks for any help
--
James

Named Ranges
 

gocush,
Thank you very much for your continuing help, I'm at home but I'll try this
the first thing in the morning.
You are right about where I want the cell to "appear in the upper left
corner of the monitor screen to match whatever week it is today."
Again thank you so much,
James

"gocush" /delete wrote in message
...
From you OP:
Depending on where the worksheet was last closed the named cell will be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left cornor?

also from you op I gather that you have named cells on a sheet for Smith:
Smith_Week_1 ..... to Smith_Week_2.

First of all these named cells are Named Ranges. A Named Range is simply
a
range of cells (one or more) that you have given a name to, either in the
Name box or thru InsertNameDefine

I think what you are trying to achieve is this:
Whenever you activate the Smith sheet, the cell Smith_Week_xxx will appear
in the upperleft corner of the monitor screen to match whatever week it is
today.
If this assumption is correct then
1. You need a way to determine a date when Week_1 begins. Let's say it's
1/1/05

2. On a new Worksheet (call it "K"), you could set up a table and enter
1/1/05 in A1 and 1 in B1. Then fill down in col B 1-20. Enter in cell A2
the formula: =A1+7. Copy this formula down to A20, which will give you
the
starting date for 20 weeks.

3. Select this table (A1 to B20) and give it a Name: WeekTable.

3. In C1 enter the formula =VLookup(Today(),WeekTable,2)
Give C1 the name: ThisWeek
(This should result in a number from 1 to 20 depending on which week we
are in.)

4. Rt-Click on the TAB at the bottom of the sheet that says Smith. Then
click on View Code.

5. In the rt panel enter the following

Public Sub Worksheet_Activate
dim i as integer
i = Sheets("K").Range("ThisWeek")
ActiveWindow.ScrollRow = Range("Smith_Week_" & i).Row
ActiveWindow.ScrollColumn = Range("("Smith_Week_" & i").Column
End Sub

This is called a Worksheet_event. It is triggered, or is "fired off"
every
time this sheet is activated.
If this works for the Smith sheet the way you want, then you can expand it
in a couple of ways. First, as it is, you will have to manually change
the
start date(A1), perhaps quarterly. You can add vba code so start date
changes automatically.
Next you can expand this by assigning the name of the sheet as a variable,
like we did with i (the week number).

You would then use the module in the vb editor called ThisWorkbook and
enter
code in the following event:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
.............
End Sub

This works for all sheets in ThisWorkbook. The variable Sh is the sheet
name: Sheet.Name

Work with this for a while and see if it gets you started.

"James" wrote:

gocush,
I'm kind of at a loss here, how do I use this with a hyperlink?
I have about 20 cells that have hyperlinks in them, do I write this code
for
each one and then put the named range in the ("MyCell") place?
Thanks for your assistance
James

"gocush" wrote:

Sub ScrollMyCell()
ActiveWindow.ScrollRow = Range("MyCell").Row
ActiveWindow.ScrollColumn = Range("MyCell").Column

End Sub

"James" wrote:

Hello NG,
I'e been using Excel for a while but I'm new to Named Ranges.
I have a column of hyperlinks, on a master page, to cells I've named
on
indiuidual pages.
example on the sheet for Smith in cell A2 I call it Smith_Week_1
and again on Smith in Cell A18 I call it Smith_Week_2.
and so forth down the page for about 20 different cells.
I do this for 9 employees.
Depending on where the worksheet was last closed the named cell will
be in
the middle of the page or at the top or at the bottom.
Is there a way to always make the named cell be in the top left
cornor?
Thanks for any help
--
James