# Copy Formulas and Create Sheet Name References

#### ajones

##### Board Regular
I have a large date sheet that I have been working on. While testing out various formulas and making sure I get what I want right I have been putting the basic summary formulas on the same sheet as the data sheet. I am now getting to the point of creating a summary sheet.

I was wondering if anyone knew of an easy way to copy and paste formulas and have them update so they referenced the original sheet and not the new summary sheet?

Simple Example: sheet1 in cell f23 might have =sum(a1:a30000)

I want to copy that formula to sheet2 in cell b2 and have it say something like =sum(sheet1!a1:sheet1!a30000).

So I don't have to go in an reedit all the formulas ...

thanks

Alan

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Alan,

The quickest way for you is to CUT the formulas and paste them to the summary sheet. They will continue to reference the correct cells on the original sheet.

The syntax for referring to cells on another sheet is --
1. If the sheet name is one word:
=SUM(Sheet1!B2:B30000)

2. If the sheet name is more than one word:
=SUM('Sheet 1'!B2:B30000)

Note the single quotes for multi-word sheets names. NEVER use apostrophes in the sheet name. It will prevent you from referring correctly to that sheet.

Denis

Denis,

Thanks for the response... I did not realize that CUT worked different then COPY in this regard. However if possible I would like to do a COPY and not a CUT.

However my original formulas do NOT have sheet names and I would like if possible to do a COPY.

sheet1 might have =sum(a1:a30000)
I want to copy that formula to sheet2 have it say something like =sum(sheet1!a1:a30000).

I am basically trying to find an easy way to insert the original sheet names in the copy process or or before the copy process.

I guess I could cut paste to the new sheet then copy back to the original, but I was thinking there would be a more elegant way....

I hoped there was a paste special option I was not aware of but did not see one.

thanks again for the response...

if you have any other suggestions I would appreciate it.

thanks

Alan

Hi Alan,

You've got 2 main options.

1. Use CUT to transfer the formulas as I mentioned, thus removing them from the original sheet

2. Link the summary sheet to the main data sheet. Let's say you want a total that appears in C21 of Sheet1 to appear in A5 of the Summary. Do this:
a. Select Summary!A5
b. Press =
c. Select Sheet1, select cell C21, and press ENTER

Repeat the process for any other cells whose value you want in the Summary.

Denis

Replies
3
Views
1K
Replies
6
Views
990
Replies
10
Views
380
Replies
11
Views
922
Replies
6
Views
448

1,203,534
Messages
6,055,956
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back