help me excel at excel
October 13, 2011 10:47 AM   Subscribe

Excel-filter: Is there a way to copy a formula from a cell on one sheet (e.g., page 4) to a different cell on a different sheet (page 1), where the total from page 4 is carried over to page 1?

This is probably an easy question for Excel-experts, but I am an Excel-noob. Currently working in 2003 but upgrading soon to 2010, so answers for both/either are great.

I have a spreadsheet wherein each page is a running total-by-month of expenditures. So for example, page 4 is a particular budget item, and cell A1 uses the formula =SUM(SUM(N:N)-SUM(O:O)) to total debits minus credits for that item.

Page 1 of the same file has a list of all the budget items with the current total for each. So cell D5 on page 1 has the same total as cell A1 on page 4, and that total changes every month. What I've been doing is copy/pasting from one cell to the other, but I'd like for it to automatically update. How can page 1 learn what page 4 knows without me telling it?
posted by headnsouth to Computers & Internet (16 answers total) 1 user marked this as a favorite
 
Best answer: I believe this works for all recent-ish versions of Excel. If Sheet1 is really named "Sheet1": put the following formula on Sheet 4.

=Sheet1!D5

If you put an = into a cell to enter formula mode, then change sheets and click on a cell, Excel will automatically put together the reference name, same as referencing different cells in the same sheet.
posted by Alterscape at 10:53 AM on October 13, 2011


Unless I'm missing something, is this not as simple as D5='Page 4'!A1
posted by Zophi at 10:53 AM on October 13, 2011


Just type an = and then click on the cell you want to copy.
posted by empath at 10:54 AM on October 13, 2011


Response by poster: Just type an = and then click on the cell you want to copy.

When I do that it tells me the formula contains an invalid reference. Would that be the page name?
posted by headnsouth at 10:59 AM on October 13, 2011


Are you typing = then clicking on the page and cell you want to reference?
posted by Zophi at 11:03 AM on October 13, 2011


Yes... I'm looking at my own Excel spreadsheet. A cell I'm looking at contains:
='Main order form'!L11283
If I look down at the page tabs at the bottom, one says Main order form.
posted by crapmatic at 11:03 AM on October 13, 2011


Copy the cell in the first sheet
Go to the second sheet
Use Paste Special>>Paste Link.

It will put all the necessary info in there for you. If you rename the sheets later, it may cause you some issues, but just repeat the process.
posted by lampshade at 11:05 AM on October 13, 2011


Does row L really contain 11283 entries? That looks typo-y to me, but maybe you have a reaaaally big spreadsheet?
posted by Alterscape at 11:09 AM on October 13, 2011


L is the column, not the row, and at least in my work, 12,000 rows is a fairly medium-sized spreadsheet.
posted by brainmouse at 11:13 AM on October 13, 2011


I had this problem when my worksheet had non alphanumeric characters in the title. I deleted them, and it worked fine after that.
posted by Jairus at 11:23 AM on October 13, 2011


Response by poster: You guys are great: =Sheet4!A1 works a treat.

Followup: just now, IT called to tell me I'm getting a new PC and Office 2010 in the next few days (yay!). Is Excel 2010 different from 2003 in this regard? (And are there any helpful 2003-2010 tricks I should know?)
posted by headnsouth at 11:31 AM on October 13, 2011


The ribbon interface. Familiarize yourself with it.

Microsoft has some non-hateful Office 2010 training courses as well.
posted by beefetish at 11:42 AM on October 13, 2011


This particular thing works exactly the same.
posted by empath at 11:43 AM on October 13, 2011


The ribbon interface in 2010 has a learning curve and is therefore kind of a pain in the balls, but the help section is shockingly helpful. Huge, huge improvement over earlier versions of office. And yes, this particular formula works exactly the same as it did in 2003.
posted by 8dot3 at 12:04 PM on October 13, 2011


beefetish: "The ribbon interface. Familiarize yourself with it."

Seconded.

Also if your IT is nice enough and willing to spend the money, this add-in was extremely helpful to me when I first upgraded to the newer Office version.

Classic Menus for Office
posted by lampshade at 12:21 PM on October 13, 2011


If you put a little dollar sign in front of a column or row, it'll make that value stick in a formula, as opposed to helpfully changing when you move it around if you happened to paste it elsewhere.

So this:
=Sheet4!$A$1

Will make the formula always look to that cell. This, on the other hand, would make the formula look always at that column (column A), but would change the row value if you were to copy the formula to somewhere else where Excel wanted to helpfully change the value for you.

=Sheet4!$A1

This would allow the column to change, but not the row:

=Sheet4!A$1

It's handy, trust me!
posted by A Terrible Llama at 12:37 PM on October 13, 2011 [3 favorites]


« Older Travel with Golf Set   |   More, please. Newer »
This thread is closed to new comments.