Retrieving data from same cell from a list of worksheet names

[TMAZZ]

[TABLE="width: 331"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Not sure what I'm doing work but I'm having problems retrieving data from the same cell from many worksheets.

The formula is returning an error can you help me? Please[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]=INDIRECT(""&B20&"'!J6)[/TD]
[/TR]
</tbody>[/TABLE]


This column lists all worksheets ie.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]'=INDEX(sheetlist,ROW())[/TD]
[TD]SAM[/TD]
[TD]'INDIRECT("'"&A20&"'!J6)[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD][/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]DAVID[/TD]
[TD][/TD]
[TD]3%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want the third column to look at Column A Row 20 which reads worksheet name SAM and in that worksheet drop the data from cell# J6,
This is repeated as many worksheet exist so the next worksheet name is John and in that worksheet cell# J6 is 5% and so on

 

[Momentman]

If the name is strictly what you have typed in column A, then maybe

=INDIRECT(A20&"!"&"J6") or

=INDIRECT(A20&"!"&ADDRESS(6,10))

 

[TMAZZ]

The following formula is in column A
INDEX(sheetlist,ROW())
then I tired typing in the name SAM in column A
Still returning a error #REF!

 

[Momentman]

Well, except SAM, JOHN ... are not the exact names of your shee 'cos see what I have here, I created sheets with those names and used with that formula

Excel 2010
	A	B	C
20	SAM		5%
21	JOHN		2%
Sheettoday`2

Cell Formulas
Range	Formula
C20	=INDIRECT(A20&"!"&"J6")
C21	=INDIRECT(A21&"!"&"J6")

 

[TMAZZ]

Sorry Still not working, Anyone else have an idea why its not working?

Still returning a error #REF!

 

[Momentman]

Sorry Still not working, Anyone else have an idea why its not working?

Still returning a error #REF!

I know you have tried with a formula and by typing in the name of the sheet, have you confirmed that the sheet name does not contain extraneous spaces because there's no reason why the formula as I proposed shouldn't work

 

[TMAZZ]

I know you have tried with a formula and by typing in the name of the sheet, have you confirmed that the sheet name does not contain extraneous spaces because there's no reason why the formula as I proposed shouldn't work

OK it works as shown below, but why does it not work with the formula? I notice the formula adds a space as noted below, here's the formula for the worksheets
'=INDEX(sheetlist,ROW())
how do I get the above formula to start without a space?


[TABLE="width: 203"]
<tbody>[TR]
[TD]' JOHN[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]0.0366972[/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

The formula picks from "sheetlist" which is a range name(I think , so I would suggest you check the "sheetlist" range for those spaces, 'cos the formula simply returms what you have in sheetlist range.

Victor

 

[TMAZZ]

The formula picks from "sheetlist" which is a range name(I think , so I would suggest you check the "sheetlist" range for those spaces, 'cos the formula simply returms what you have in sheetlist range.

Victor

I tried =TRIM(A20) this removes the space and the results sits in B20 but then the formula


[TABLE="width: 100%"]
<tbody>[TR]
[TD]=INDIRECT(B20&"!"&"J6")

doesn't work. note the formula is changed from A20 to B20[/TD]
[/TR]
</tbody>[/TABLE]

 

[TMAZZ]

Never mind I got it to work thanks