moar excel helpy!

[TdC]

folks,

I have a line of text in one cell (call it 'Sheet1!A1'), which could be a formula if it had an equals sign in front of it, and another cell (call it 'Sheet2!A1') which I want to to have the formula in.

So, I want to put `='Sheet1!A1'` in cell 'Sheet2!A1' and have it evaluate as a formula, with all cell referenced in the formula text being relative etc etc etc. I do NOT want to use the lunacy that is VBS.

So far I have tried =INDIRECT() and putting things in directly, but nothing works. I hope there is a way... Surely I am not asking something too stupid?

 

[TdC]

I've given up. Any kind of useful automation also replaces my PERFECTLY VALID CELLS as #REF! for some reason, so 'Sheet1!A1' becomes 'Sheet1!#REF!' the moment I paste it. Fuck this

 

[Moriath]

why are you doing excel at 2am on a sunday morning near xmas instead of drinking ?

 

[TdC]

don't look at me!! DONT LOOK AT MEEEE

 

[MYstIC G]

Not at pc but ' stops a formula being evaluated at the beginning of a cell. Presumably that can be used to assist.

 

[Jupitus]

Note sure WTF you want to do this, but anyway, this might help.

Type formula in cell, e.g. 'MAX(5,10)'

Highlight that cell, and do insert > name > define

Name the cell something, I used 'F_Cell' and in the 'refers to' box typ in '=EVALUATE(Sheet1!$A$1)' <<< or whatever cell you wanted

Hit enter.

In cell where you wanted the result of the text function simply type =F_Cell or the name you have chosen for your formula cell. Not practical for doing lots of these though...

 

[TdC]

here's the thing:

I have an SQL statement as some text in a cell. Call it SELECT {blank} FROM sometable
what I want to do is have{blank} be filled by an excel formula.
the excel formula is in another cell, because it's still being worked on, so I want to keep it in ONE PLACE, and have the rest of the workbook update dynamically as the formula changes. maybe this is wrong, but it seems (to me) to be the logical way. ie, I only change one thing rather than having to replace 20k lines. The formula is in one place, but it references cells that have to change dynamically as per in what row it's placed, so say the formula is ('somesheet'!A1+'someothersheet'!A1) according to whatever row it's placed in it should be for row number X ('somesheet'!AX+'someothersheet'!AX)

 

[Jupitus]

What I'd do is create a list of cell names going down in their own column alongide the other 20k whatever (use autofill for numbering) and then do a quick VBA routine to perform the insert > name > define step and the insertion of the =F_Cell step automatically. Can't do it for you, I've left work for the day - let me know if you want me to do it when back at the office

 

[TdC]

yeah no vb. based purely on the fact that I personally hate it, and that anyone else using my sheet will then have to install the script. which is lame (imo). but cheers anyway Jupbacon, you're a star for even bothering to respond :-D