Can a hyperlink be embeddied in a function?

[dond]

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

 

[Ardus Petus]

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

HTH

 

[dond]

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

 

[Ardus Petus]

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

What else did you expect?

Cheers,

 

[dond]

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?

 

[Ardus Petus]

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

 

[dond]

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

 

[Ardus Petus]

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

HTH