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.

 

[AnotherHiggins]

I want to do this programmatically... and I don't want to use ... programming systems.

Huh?

How [/i]do[/i] you expect to do it if not with any programming system and not with keystrokes?

Do you mean you want to create a dynamic link to the other cell? What results would you expect to see in cell A3 on Sheet 1: 3 or 7?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

HUH?

Programatically but NOT VBA??? Oxymoron?

Well WHICH programming language do you wish to use, not that your wish necessarily means that it can materalize?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MakeItSo]

Hi Mike,

depsite your description being cryptic and in itself contradictory, would your solution be simply this formula on Sheet1, A3:

=Sheet2!A3

?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[GlennUK]

Hi Mike,

is that you want to be able to specify Sheet2!A3 and have Excel return the formula from that cell somehow? And, if so, for what purpose?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BNPMike]

Hi Higgins

"How [/i]do[/i] you expect to do it if not with any programming system and not with keystrokes?"

Probably I need a function eg CONTENTS() which substitutes the data entered into the cell concerned, not its value, not its sheet reference and no intelligent changes - just what was typed in.

To address your separate question, I'm sure lots of experts know how to do this in VBA or COM etc. I'm looking for the experts that know how to do it in Excel (if you can, of course). When I say Excel I mean basic operators, functions, intelligent-copy-and-paste etc.

"Do you mean you want to create a dynamic link to the other cell? What results would you expect to see in cell A3 on Sheet 1: 3 or 7? "

In this case I want 3. If I do any normal referencing I get 7. I can get Sheet 2 to refer to Sheet1 but that's creating more links than I want.

My motivation is to re-use source code.

 

[SkipVought]

Please explain functionally WHAT you want to accomplish rather than telling us HOW you think it ought to be done.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

Hi Glenn

"is that you want to be able to specify Sheet2!A3 and have Excel return the formula from that cell somehow? And, if so, for what purpose? "

That's what I want.

I guess I want it so I can create a workbook LibGlenn, then I can write other workbooks that want to use LibGlenn logic, so they can copy LibGlenn into their worksheets but not the bits that provide input to relevant functions. Normal Excel means LibGlenn needs to know up front who is going to call it so it can refer to the calling worksheet's input cells. It would also have to have an instance of the logic for every possible call. That is not impossible but far too constraining.

You can of course share VBA code by various means, but I just had this funny feeling I'd read there was away to access Excel code in cells, bare-back.

 

[MakeItSo]

Take a look at this here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

That what you're looking for?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

NO VBA!!! a mantra

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MakeItSo]

well, although it is VBA, it is a User defined function and there does not seem to be a built-in function to achieve this.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

But the OP specifically and unequivocally stated, "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."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

you state, "A3: =CONTENTS(Sheet2!A3) ie I want it to say =SUM(A1:A2)"

The "contents" of a cell can be returned, using the CELL() function, however, your definition of "contents" and Excel's definition are at odds.

Seem that the best you might be able to do, with this severe, self-imposed restriction, is to turn on the Show Formulas in cells instead of their calculated results checkbox in Excel Options. But that doesn not seem as if it would really help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

Well, looks like there isn't an in-built function, from what you're saying.

Just while we're on this subject of 'source', dare I ask the next question:

A1: CELL
A2: "contents"
A3: "C6"

From these, can I generate in A4: =CELL("contents",C6)

 

[SkipVought]

Did you try it?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

=CONCATENATE("=",A1,"(",A2,",",A3,")")

Randy

 

[BNPMike]

Well the formula is there but it is behaving as a string not a formula. I'm in 2000 at the moment and it doesn't allow you to format a cell as Formula. I never realised you could start a string with an = without it being enclosed in quotes.

 

[SkipVought]

No version of Excel, that I am aware of, has a cell format of Formula!

A cell containing a formula, can have any NUMERIC format, other than TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

Skip - That's my point. The cell contains a formula but Excel is treating it as a string. You would have thought you could ask Excel to treat the string as a formula.

 

[SkipVought]

How is the cell in question formatted?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]