Excel: How do you copy the contents of a cell?

[BNPMike]

If I have Sheet 2:

A1: 1
A2: 2
A3: SUM(A1:A2)

Then I have Sheet 1:

A1: 3
A2: 4
A3: =CONTENTS(Sheet2!A3) ie I want it to say =SUM(A1:A2)

To clarify: I want to do this programmatically (ie not with the UI) and I don't want to use VBA or other programming systems.

 

[N1GHTEYES]

I think you might be able to get partway to what you are after using the evaluate function.

Basically, I think what you are trying to do is set up a series of functions, then use some other part of the worksheet to conditionally determine which function is applied to some conditionally-specified data. I suspect that in almost all cases it would be easier to do this using "normal" Excel methods, but use of Evaluate might make for an interesting alternative.

So here goes:

First, define a single-cell named range called x. If you like, you can make the range the result of an offset function so that by simply changing the value of one or two cells, you can control which cell "x" refers to.

E.g. put the value 3, say, into cell B1 then any other value you like into cells B2 to B6, say.

Now define x as: =offset($B$1, $B$1,0,1,1).

So, if B1 = 3, then x = the value in cell B4.

Now set up E1 = 2, and:

E2 = "sin(x)"
E3 = "cos(x)"
E4 = "x*log(1/(1+(cos(x)^2)))"

...or any other formulae you like.

Then define the name "genfunc" as = EVALUATE(offset($E$1,$E$1,0,1,1))

So, if E1 = 2, then genfunc = "=cos(x)".

Now type =genfunc into any cell, say F1.

You should get the value cos(B4) in cell F1.

I know that looks complicated, but I've tried it an it works. Try it out and see.

Basically it means you can perform conditionsl function calculations. I think that, with a little imagination it should be possible to do things this way which would be impossible, or at least very much more complicated, using "normal" Excel methodologies.

However, my imagination starts to fade into the background after 2 am, and it is now 02:24, so I'll leave that as an exercise for the morning people...

Tony

 

[BNPMike]

I can see why you're called Nighteyes.

What you've suggested is using a library function. It's actually an Excel 4 Macro.

To expand a little on what I'm up to: Firstly I would like to protect the user from imperative programming. The user has got used to Excel and its IDE. They can therefore examine and display the contents of 'variables' and evaluations of sub-expressions without having to learn how to run and debug conventional programs. EVALUATE fits in with this. They're not programming anything. We assume the function is already fully tested and reliable. You could extend this idea to create a set of VBA library functions that fill in Excel gaps. So the first issue I have is if you want to use a user defined function then you might as well do it in VBA. However I'm nervous about that for the reason discussed next.

The second aim is to keep the code inside what Microsoft build and control. The reason for this is Excel is already designed to automatically run concurrently across many threads/cores. It would appear Microsoft will keep extending this ability to use multi-core computers. The future is multi-core, and it's not trivial to arrange this with traditional imperative programming languages. I am not aware of any easy way to do this for non-programmers. By contrast Excel needs to maintain a dependency graph, so it already has the information - all the time - to identify which 'legs' can be evaluated at the same time as other legs without complications. My concern is where UDFs fit into this. I think you risk Excel isolating these functions and evaluating them more in-series. In the particular case of EVALUATE, you have the problems that at some stage (Office 2xxx)they won't work at all.

I've only just started down this path, as you can see. It is possible that the only way to make Excel a more general programming framework, I will need to think about a set of such library UDFs.

 

[N1GHTEYES]

I think one example of how the Evaluate method might come into its own would be if, say, you were trying to create an evolutionary equation-solver (a la Eureqa).

I guess that, in theory, one could use this methodology, combined with workbook iteration, to implement an algorithms to "breed" best-fit equations. To be honest though, I struggle to see too many other uses for it. Having said that, I do have an "equation-display" tool which uses Evaluate to display any equation the user enters as text. It is a considerably tidied-up version of one a colleague found on the net eons ago.

Tony

(So this is what morning looks like huh? I'm not impressed.)