I have a Worksheet for each week of the year named Week 1 to Week 5
(pretty original I know). In a seperate worksheet I need to reference
range of cells to locate a cell in each of the 52 other worksheets.

So begginning in cell E1 I have ='Week 1'!$G$121
in cell E2 I have ='Week 2'!$G$121
in cell E3 ='week 3'!$G$121


Can I replace the reference to week 1 with a hyperlink so that I ca
drag the equation across the cells and the week (Worksheet) referenc
increases leaving the remaining formula as is?
I could do this manually but there are hundreds of functions t
reference this way and I assume there must be a way to do this.

Can anyone help me out here

--
don
-----------------------------------------------------------------------
dond's Profile: http://www.excelforum.com/member.php...fo&userid=2547
View this thread: http://www.excelforum.com/showthread.php?threadid=54642

In E1, enter:
=INDIRECT("'Week "&ROW()&"'!$G$121")
and drag down

HTH
--
AP

"dond" <(E-Mail Removed)> a écrit dans le
message de news: (E-Mail Removed)...
>
> I have a Worksheet for each week of the year named Week 1 to Week 52
> (pretty original I know). In a seperate worksheet I need to reference a
> range of cells to locate a cell in each of the 52 other worksheets.
>
> So begginning in cell E1 I have ='Week 1'!$G$121
> in cell E2 I have ='Week 2'!$G$121
> in cell E3 ='week 3'!$G$121
>
>
> Can I replace the reference to week 1 with a hyperlink so that I can
> drag the equation across the cells and the week (Worksheet) reference
> increases leaving the remaining formula as is?
> I could do this manually but there are hundreds of functions to
> reference this way and I assume there must be a way to do this.
>
> Can anyone help me out here?
>
>
> --
> dond
> ------------------------------------------------------------------------
> dond's Profile:
> http://www.excelforum.com/member.php...o&userid=25477
> View this thread: http://www.excelforum.com/showthread...hreadid=546428
>

Tried the formula but its not happening. Is there something that I need
to do to your formula to reference it to the worksheets?


--
dond
------------------------------------------------------------------------
dond's Profile: http://www.excelforum.com/member.php...o&userid=25477
View this thread: http://www.excelforum.com/showthread...hreadid=546428

Works by me!
My formula references 'Week n'!$G$121
n being the row number.

What else did you expect?

Cheers,
--
AP


"dond" <(E-Mail Removed)> a écrit dans le
message de news: (E-Mail Removed)...
>
> Tried the formula but its not happening. Is there something that I need
> to do to your formula to reference it to the worksheets?
>
>
> --
> dond
> ------------------------------------------------------------------------
> dond's Profile:
> http://www.excelforum.com/member.php...o&userid=25477
> View this thread: http://www.excelforum.com/showthread...hreadid=546428
>

Regarding your function

In E1, enter:
=INDIRECT("'Week "&ROW()&"'!$G$121")
and drag down

You are right. You formula does work! Although I am still trying to
work out why.

Unfortunately when describing my problem I used the reference to cell
E1 as an example as I felt that any resulting formula would work the
same in any other cell if placed there. This does not seem to be the
case. I tried placing it in cell E175 and it keeps returning the REF
result yet I cannot see where your fomula is specific to the entered
(E1) cell to make a change.

The other matter was that I wanted to reference the G121 row as in your
equation but I want the formula results so I could drag the formula
across the row not down the column.

Can you be of assistance?


--
dond
------------------------------------------------------------------------
dond's Profile: http://www.excelforum.com/member.php...o&userid=25477
View this thread: http://www.excelforum.com/showthread...hreadid=546428

My formula takes the current row # and adresses the corresponding week
sheet.

If you want it to be placed in ANY column, starting with Week 1, and
right-draggable, it would be:
=INDIRECT("'Week "&COLUMN()-COLUMN("A:A)+1&"'!$G$121")

HTH
--
AP

"dond" <(E-Mail Removed)> a écrit dans le
message de news: (E-Mail Removed)...
>
> Regarding your function
>
> In E1, enter:
> =INDIRECT("'Week "&ROW()&"'!$G$121")
> and drag down
>
> You are right. You formula does work! Although I am still trying to
> work out why.
>
> Unfortunately when describing my problem I used the reference to cell
> E1 as an example as I felt that any resulting formula would work the
> same in any other cell if placed there. This does not seem to be the
> case. I tried placing it in cell E175 and it keeps returning the REF
> result yet I cannot see where your fomula is specific to the entered
> (E1) cell to make a change.
>
> The other matter was that I wanted to reference the G121 row as in your
> equation but I want the formula results so I could drag the formula
> across the row not down the column.
>
> Can you be of assistance?
>
>
> --
> dond
> ------------------------------------------------------------------------
> dond's Profile:
> http://www.excelforum.com/member.php...o&userid=25477
> View this thread: http://www.excelforum.com/showthread...hreadid=546428
>

I used the formula =INDIRECT("'Wee
"&COLUMN()-COLUMN("A:A)+1&"'!$G$121") and kept recieving an error
highlighting the three accents that were placed after the & thus
&"'!$G$121") Any attempt to change the formula resulted in it becomin
a text string only.

To try and get some understanding on this the calculations that
entered manually were : Cell E172 ='Week 1'!$G$121 , Cell F172 ='Wee
2'!$G$121 , Cell G172 ='Week 3'!$G121 continuing on until I reached
cell BD172 with ='Week 52'!$G$121. The next row in Colomn E173 bega
='Week 1'!$G$122 continuing across until I reached Cell BD173 in
similar fashion to the 172 row.
Unfortunately I have another 20 rows to do in this fashion and I wa
trying to find a way of entering the 1st cell as in E172 then draggin
right to generate automatic calculations where only the reference t
the worksheet changed.

I really do appreciate the help and maybe its my lack of understandin
that is not helping but these problems do keep the brain active.
As before any help is appreciated. I will keep entering manually unti
a solution is recieved. Thanks for the help so far

--
don
-----------------------------------------------------------------------
dond's Profile: http://www.excelforum.com/member.php...fo&userid=2547
View this thread: http://www.excelforum.com/showthread.php?threadid=54642

Typo:
=INDIRECT("'Week "&COLUMN()-COLUMN($D175)&"'!$G$121")
see example: http://cjoint.com/?fEkG68lNnc

HTH
--
AP

"dond" <(E-Mail Removed)> a écrit dans le
message de news: (E-Mail Removed)...
>
> I used the formula =INDIRECT("'Week
> "&COLUMN()-COLUMN("A:A)+1&"'!$G$121") and kept recieving an error
> highlighting the three accents that were placed after the & thus
> &"'!$G$121") Any attempt to change the formula resulted in it becoming
> a text string only.
>
> To try and get some understanding on this the calculations that I
> entered manually were : Cell E172 ='Week 1'!$G$121 , Cell F172 ='Week
> 2'!$G$121 , Cell G172 ='Week 3'!$G121 continuing on until I reached
> cell BD172 with ='Week 52'!$G$121. The next row in Colomn E173 began
> ='Week 1'!$G$122 continuing across until I reached Cell BD173 in a
> similar fashion to the 172 row.
> Unfortunately I have another 20 rows to do in this fashion and I was
> trying to find a way of entering the 1st cell as in E172 then dragging
> right to generate automatic calculations where only the reference to
> the worksheet changed.
>
> I really do appreciate the help and maybe its my lack of understanding
> that is not helping but these problems do keep the brain active.
> As before any help is appreciated. I will keep entering manually until
> a solution is recieved. Thanks for the help so far.
>
>
> --
> dond
> ------------------------------------------------------------------------
> dond's Profile:
> http://www.excelforum.com/member.php...o&userid=25477
> View this thread: http://www.excelforum.com/showthread...hreadid=546428
>