Retrieving data from same cell from a list of worksheet names

[TMAZZ]

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 =INDIRECT(""&B20&"'!J6) <tbody> </tbody> This column lists all worksheets ie. '=INDEX(sheetlist,ROW()) SAM 'INDIRECT("'"&A20&"'!J6) JOHN 5% DAVID 3% <tbody> </tbody>

<tbody>
</tbody>

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?

' JOHN	#REF!
JOHN	0.0366972

<tbody>
</tbody>

 

[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

=INDIRECT(B20&"!"&"J6") doesn't work. note the formula is changed from A20 to B20

<tbody>
</tbody>

 

[TMAZZ]

Never mind I got it to work thanks