Import CSV File and include formulas

[travelingwilly]

Hello folks

I wish to create a flat file with Formulas so that excel translates properly. I know that if I put =("00001") it will be be translated in the spreadsheet as 00001 instead of automaticly thinking it is a number and converting to a number 1 . But I am sure you can do the same thing with formulas....

example of csv

=("00001"),34,34,21,=formula(r[1]c[-3]+r[1]c[-3])
=("00002"),34,34,21,=formula(r[1]c[-3]+r[1]c[-3])
=("00003"),34,34,21,=formula(r[1]c[-3]+r[1]c[-3])
,,,,=sum(r[1]c[1]:r[3]c[1])

Or somthing like that. Is there away to this.
Basicly I want the formula to be used not the value so when it is loaded I can change a value and I will see the change without adjusting the sheet...

Make sense ???

 

[Jim North]

travelingwilly,

You can if you are reading in the .csv file via a VBA procedure... how are you loading the file into Excel?

 

[Mark W.]

Try this...

="00001",34,34,21,=rc[-3]+rc[-3]
="00002",34,34,21,=rc[-3]+rc[-3]
="00003",34,34,21,=rc[-3]+rc[-3]
,,,,=sum(r[-1]c:r[-3]c)

...but, before you do make sure that the R1C1 reference style is set. See the Tools | Options... menu command's General tab.

 

[travelingwilly]

That works great. It is unfortunate that you have to use the RC reference to have it work properly. But at least you can Download actual Formula to a spreadsheet. Or is there anouther way using maybe XML ???

 

[Mark W.]

Not quite sure why you've concluded that you must use the R1C1 reference style.

This works too...

1000,2000
1500,2500
=SUM(A1:A2),=SUM(B1:B2)

 

[travelingwilly]

Thanks I was thinking about using RC because it was easer to program . But now that I think of it it seems easier with the Col References.