Referencing a function instead of the value of the function

[AKruschev]

Hi there,

I am trying to paste a function (e.g. =SUM(A1:A10)) from a different sheet by using

sheet1!$A$1. This returns the value of the sum in sheet 1 instead of the function itself.

Is it possible to get the sum of A1:A10 in sheet2 without explicitly writing "=SUM(A1:A10)" in that sheet?

[#2]

@AKruschev 

I am trying to paste a function (e.g. =SUM(A1:A10)) from a different sheet by using sheet1!$A$1. This returns the value of the sum in sheet 1 instead of the function itself. Is it possible to get the sum of A1:A10 in sheet2 without explicitly writing "=SUM(A1:A10)" in that sheet?

First, I'm sure it's possible, but...

Second, What you say doesn't really make sense.

Let me explain why: You say you're trying to do thus and so, and that what happens is "this returns the value of the sum instead of the function itself." OK, so far, you with me? And am I accurately quoting?

You then ask,  "Is it possible to get the sum of A1:A10 in sheet2"; is that also still correct? That's what you've said?   But the value of the sum, and the sum are surely one and the same!!  So you appear to be asking, is it possible to get what I'm getting--which is at the very least confusing.

To attempt clarification, I've attached a workbook containing Sheet1 and Sheet2 

• There's a series of numbers in Sheet 1 A1:A10
• There are formulas in Sheet 1, cell C1 and in Sheet 2 , cells A1 and A4. Each of these three formulas is unique.
• Is one of those formulas what you want?

If not, please come back with a restatement of what you're trying to do.

[#3]

Thank you for your response @mathetes.

To get more specific, I am trying to use a switch case in sheet 2 to use different formulas on the data in sheet 2. Sheet 1 serves as a reference sheet so I can update or add new formulas in the future and have them update across all other sheets. Currently sheet2!C1 outputs whatever is in the placeholder area in sheet 1 instead of sheet 2.

---

---

I hope this helps clearing things up. Best regards!

[#4]

@AKruschev 

It's a step in the right direction of clearing things up. I've never used SWITCH before myself, but from what I read about it here, it looks to me as if it only returns values, not formulas. Have you experience with using SWITCH before, in a way that returns formulas?

That said, I wonder if a combination of IFS or CHOOSE and INDIRECT might give you the result you're seeking. With INDIRECT in particular, it is possible to "create" a new formula from parts. It's also a resource-hog of a function, though, so if you were to be employing it widely, your workbook could be very slow.

You've really only shown us, in your images, the tip of the iceberg. Is it possible for you to post a copy of the actual workbook (or sample workbook) with more of the complete iceberg--i.e., examples of those other places in Sheet2 where the formulas exist that you want to have control over. That way, I (or one or more of the others here in the forum who seek to help by answering questions) can more quickly (a) understand the big picture, and (b) test out alternate formulations. Post the actual (sample) workbook on OneDrive or GoogleDrive with a link pasted here that grants access to it.

It also would help if you would be willing to describe the bigger purpose of the workbook, what it is about the situation that you want or need to be able to change formulas within Sheet2. That almost seems like a recipe for disaster; it certainly would be if we were dealing with financial data, for example.