copy formula in another sheet

I should copy a formula into another excel sheet, which is part of the same workbook. I click on the cell of the sheet2 and I type =, then I go to the cell of the sheet1 and I press enter key. here the problem starts: for some reason, the copied formula is not the one I see on the formula bar by clicking on the cell of the sheet1, but a completely different formula

3

3 Answers

Select the cell containing the formula you want to copy and select the formula from the formula bar and copy it. Now you can paste it anywhere you want to have the same formula.

It depends on what you asking. What you are doing is referencing the cell so the formula in sheet2 will be Sheet1!A1 or whatever cell you're pointing to. If you want to actually get the formula then If you are using Excel 2013 or greater you can set the formula in the cell of sheet to =FORMULATEXT(Sheet1!B1)

If you want to take it a step further and apply the same formula in the same manner it is applied in Sheet1 then follow these steps.

1. In my example I set Sheet1!A1 to 45 and Sheet1!B1 '=COS(A1)' (no quotes) This is what Sheet1 looks like:

2. Then I created a name thanks to a handy trick still available. Click on Name Manager under the Formulas Tab and create a New Name called EVALUATE. Set Refers to: =EVALUATE(OFFSET(INDIRECT("RC",FALSE),0,-1))Name Manager Example

3. Lastly, I set Sheet2!A1 to 90 Sheet2!B1 = FORMULATEXT(Sheet1!B1)and then Sheet2!C1 to =EVALUATE Sheet2 Example

Here's a snippet of the formulas in Sheet2

The value in Sheet2!C1 will be COS(90) and not COS(45) as it was in Sheet1. If this is what you were after then I hope I helped and can get some points so that I can stop looking like a noob. Take care.

1

Generally this never happens. Once you try these steps.

Suppose in Sheet 1 you have Formula in Cell A20 = Sum(A2:A15).

1. Go to Sheet 2, Select the desire Cell.
2. Press = sign.
3. Click the Sheet1 Tab.
4. Select A20 Cell and finish with Enter button.

You find the same Formula will be copied from Sheet1 Cell A20 to Sheet2 destination Cell and will look like =Sheet1!A20.

NB: You are getting Error ####. It means the Width of the Target Cell in Sheet 2 is smaller than Formula Source Cell in Sheet 1. Simply increase the Column Width in Sheet 2.

Hope this help you.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy