formula using sheet name

Duane · Apr 4th, 2002, 06:47 AM

Hey folks,

I think I've seen this but can't find it...

I've got a table on sheet1 that has a list of names running from B2:B12. Each name has its own sheet with the same name.

I want to create a formula in C2 that allows me to copy down to C12 by going to the same cell on each sheet for each name.

I tried variations of =("'"&B2&"'"&"!"$E$22) but to no avail.

Thanks to whoever can come up with this.

Derek · Apr 4th, 2002, 07:10 AM

Hi Duane

Try:
=INDIRECT(B2&"!$E$22")

regards
Derek

Duane · Apr 4th, 2002, 07:27 AM

Thanks, but I got a #REF error...?

Tommy Bak · Apr 4th, 2002, 08:37 AM

Dereks answer should work.
Are you, by any chance, trying to get data from another workbook??

regards Tommy

Ian Mac · Apr 4th, 2002, 08:49 AM

In you orginal question your referencing $E$22 on sheet1 of the book, in Derek's formula:

=INDIRECT(B2&"!$E$22")
it's looking for the name in B2 & "!$E$22"

i.e. Sheet2!$E$22

as in at E22 on sheet2. is this what you need, if not use

=INDIRECT(B2&"!"&$E$22)

this still wouldn't make any difference to the ref error.

Check to see if:

a: the name is exactly as the sheet name you want

b: there are no spaces before or after the names in the cells.

Duane · Apr 4th, 2002, 12:01 PM

Hey fellas,

It's the strangest thing... When I use a name that is one word, such as "James", and name the sheet the same, Derek's formula works.

When I name it the full name, as in "James Bond" it gives me the error - what, does Excel not like 007?!

[ This Message was edited by: Duane on 2002-04-04 11:07 ]

Aladin Akyurek · Apr 4th, 2002, 12:08 PM

Quote:

On 2002-04-04 11:01, Duane wrote:
Hey fellas,

It's the strangest thing... When I use a name that is one word, such as "James", and name the sheet the same, Derek's formula works.

When I name it the full name, as in "James Bond" it gives me the error - what, does Excel not like 007?!

[ This Message was edited by: Duane on 2002-04-04 11:07 ]

Try:

=INDIRECT("'"&B2&"'!E22")

Aladin

Duane · Apr 11th, 2002, 03:13 PM

Thanks to all of you, the mistake was mine, in that I referenced the wrong cell (should have been E2, not E22).

I got it working using both =INDIRECT("'"&B2&"'!E2") and =INDIRECT(B2&"!$E$22")

Much thanks!

Apr 4th, 2002, 06:47 AM	#1
Duane Board Regular Join Date: Mar 2002 Location: Toronto Posts: 173	Hey folks, I think I've seen this but can't find it... I've got a table on sheet1 that has a list of names running from B2:B12. Each name has its own sheet with the same name. I want to create a formula in C2 that allows me to copy down to C12 by going to the same cell on each sheet for each name. I tried variations of =("'"&B2&"'"&"!"$E$22) but to no avail. Thanks to whoever can come up with this. __________________ Regards, Duane h. Office2003 in WinXP o. Office2007 in Win7

Apr 4th, 2002, 07:27 AM	#3
Duane Board Regular Join Date: Mar 2002 Location: Toronto Posts: 173	Thanks, but I got a #REF error...? __________________ Regards, Duane h. Office2003 in WinXP o. Office2007 in Win7

Apr 4th, 2002, 08:49 AM	#5
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	In you orginal question your referencing $E$22 on sheet1 of the book, in Derek's formula: =INDIRECT(B2&"!$E$22") it's looking for the name in B2 & "!$E$22" i.e. Sheet2!$E$22 as in at E22 on sheet2. is this what you need, if not use =INDIRECT(B2&"!"&$E$22) this still wouldn't make any difference to the ref error. Check to see if: a: the name is exactly as the sheet name you want b: there are no spaces before or after the names in the cells. __________________ "Have a good time......all the time" Ian Mac

Apr 11th, 2002, 03:13 PM	#8
Duane Board Regular Join Date: Mar 2002 Location: Toronto Posts: 173	Thanks to all of you, the mistake was mine, in that I referenced the wrong cell (should have been E2, not E22). I got it working using both =INDIRECT("'"&B2&"'!E2") and =INDIRECT(B2&"!$E$22") Much thanks! __________________ Regards, Duane h. Office2003 in WinXP o. Office2007 in Win7

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 07:10 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Duane Try: =INDIRECT(B2&"!$E$22") regards Derek

Apr 4th, 2002, 08:37 AM	#4
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Dereks answer should work. Are you, by any chance, trying to get data from another workbook?? regards Tommy

Apr 4th, 2002, 12:01 PM	#6
Duane Board Regular Join Date: Mar 2002 Location: Toronto Posts: 173	Hey fellas, It's the strangest thing... When I use a name that is one word, such as "James", and name the sheet the same, Derek's formula works. When I name it the full name, as in "James Bond" it gives me the error - what, does Excel not like 007?! [ This Message was edited by: Duane on 2002-04-04 11:07 ]