Need some expert help: abt inserting names in formula

[cpis0002]

Hi, this is not just simply inserting defined names into formulas.
This is mainly for use between different worksheets.

So for example: if I have 52 sheets named: Week 1, Week 2, ..., Week 52.

And on a separate sheet I have the following:

Column A Column B
Week 1 ='Week 1'!SUM($B$3:$B$8)
Week 2 ='Week 2'!SUM($B$3:$B$8)
... and so on.

Is there a way so that in column B, I don't have to manually change the week number, but it will take the value from column A. Kind of like:
='A1'!SUM($B$3:$B$8)

I know this doesn't work, but I need something like it to work. Any ideas?

I am no good at all on Visual Basic

 

[joefrench]

So you want, from column A through column B to go Week1, Week2, Week3, etc...?

If so, enter the following function into Column A then fill across.
=concatenate("Week ",COLUMN())

 

[joefrench]

see below

test 28-Jul-07.xls
	A	B	C	D
27	Week1	Week2	Week3	Week4
Sheet2

 

[Norie]

I think what you want is INDIRECT.

=INDIRECT("'" & A1 & "'!B3:B8")

 

[xenou]

I got it to work this way...didn't find any difference between the Concatenation() and good old ampersand operator. Strangely, I couldn't get indirect to work directly (no pun intended...)...

Book1
	A	B	C	D	E
1	Tab	Concatenation	Sum	SingleFormula
2	Week1	'Week1'!$B$3:$B$8	6	6
3	Week2	'Week2'!$B$3:$B$8	12	12
4	Week3	'Week3'!$B$3:$B$8	18	18
5
6
Sheet1

 

[xenou]

Sorry, lost something in the HTML.

Formula in Cell B2 is:
="'"&A2&"'!"&"$B$3:$B$8"
Formula in Cell C2 is:
=SUM(INDIRECT(B2))
Formula in Cell D2 is:
=SUM(INDIRECT("'"&A2&"'!"&"$B$3:$B$8"))

(Ampersand versions)

Note: my first failed attempt was something like Indirect(Sum(Week1!B3:B8)) but the sum wasn't working as part of the string inside indirect...that's what I meant about using indirect directly...).

 

[Norie]

Oops, my bad.

Missed the all important SUM.

=SUM(INDIRECT("'" & A1 & "'!B3:B8"))

 

[cpis0002]

Hey, thanks a million, been wanting to find out how for a really long time