Copy Formulas and Create Sheet Name References

[ajones]

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

 

[SydneyGeek]

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

 

[ajones]

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

 

[SydneyGeek]

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