Is there a way of converting a text string in EXCEL into a formula (without using VB)?

Question

In cell A1 I've got part of a file name, say E1. In other cells I want to refer to data in a file E1Ratings.xls. I can use CONCATENATE() or & to produce a formula, but it is a text string and doesn't return a value.

Ar present I'm using EXCEL 2000.

I know I can do this with visual basic. If I have to go down that road, is there a way of making VB code execute as soon as a sheet is opened (or created from a template) rather than having to add a control object to the sheet.

Please don't make suggestions unless you know they will work. However a reference to a good tutorial for using VB in EXCEL would be appreciated.

Answer 1

Have you tried the INDIRECT() function? That can evaluate a reference stored as a string. I'm not sure exactly how you are referring to the data in another file, but maybe this might help.

Answer 2

is there a particular selection of numbers as doing that for all numbers you will prefer and english lanuage translator and that may no longer in common terms macro. As gramatically ninety 9 is a good distance distinctive from 999 so ninety 9 = length 2 subsequently postion one is form + ty the 2nd form will consistently be litleral except 0 then no translation 999 length 3 postion one is form + hundred the 2nd form as above. Then what happens 901 901 length 3 first postion as above 2nd postion 0 replace with and litral third postion. So extrapolation 954651 length 6 place a million literal 9 + hundered + And place 2 None litral 5 to fifty place 3 literal 4 + Thousand place 4 Literal six + Hundred + And place 5 None literal 5 to fifty place 6 Literal one urghhhhhhhhh good luck

Answer 3

I saw your problem in excel, but the thing is, don't rely on formula, sometimes, you can create your own way on how can you solve problems...and later, development will come and you will undergo on "algorithm" to develop your own formula...



for me, this is my way on refering other to other file in excel...



example:

given values:



file1.xls

a1 = 5;



file2.xls

e2 = 10;



Now, I want to put it to file2.xls on cell Z10, the formula should be like this:



=E2+[file1.xls]Sheet1!$A$1





equal to the value of Z10=15;



How?



E2 = is in current file so no need of file reference...

+ = of course addition...

[file1.xls] = file reference

Sheet1 = Worksheet name

!$ = means non integer

A = column reference

$ = integer

1 = row reference

Thats it...





Hope it can help you...God bless...



If you need long conversation for this problem, YM me...