formula help

[chaju]

Suppose I have column A a list of sheet's name (those sheet's name exist in that workbook), such that:
CELL A1 : apple
What I want is CELL B1 links to the sheet named in CELL A1: ='apple'!C3 ,
Is there a quicker method instead of editing each formula if I have a lot of sheet's name in column A...
Thanks for all your help

 

[Colo]

Hi Chaju, Long time no see on here
Are your formulas all simple like your Eg.?
If so, it's not so heard, i think. please try this.

<pre>
Sub test()
On Error Resume Next
Dim arr, i As Long
With Range([a1], [A65536].End(xlUp).Offset(, 1))
arr = .Formula
For i = LBound(arr) To UBound(arr)
arr(i, 2) = "='" & arr(i, 1) & "'!" & _
Right(arr(i, 2), Len(arr(i, 2)) - 1)
Next
.Formula = arr
End With
End Sub
</pre>

 

[chaju]

HAHA Nice to speak to you again...
Anywhere I don't have to use Macro to solve this problem? Any other methods?
Cheers Colo..

 

[DonkeyOte]

=indirect(a1&"!a3")

so if a1 were apple

=apple!a3

 

[chaju]

hI LASW10... IT'S SEEM DOESN'T WORK.. IT DISPLAY THE WHOLE FORMULA INSTEAD OF GIVING THE VALUE

 

[DonkeyOte]

is "apple" in speech marks in A1? If it is remove them so it's simply apple - this would explain why it's appearing as a text string.

 

[chaju]

I Have 00100 in Cell A1 (in text format).. any more ideas?

 

[DonkeyOte]

if you send me it I can fix it - probably need to see it to know why.

Is 00100 in the same workbook and will the sheet always begin with 00?