An Excel question

[gridlore]

I've done an Excel spreadsheet so we can track kshandra's revenue at Stupid Shuttle. I've taught it all sorts of tricks, like figuring how much she's making an hour, weekly and monthly totals and the like, but there's one thing I can't figure out.

I have the book separated into worksheets for each month, with a year end sheet. I'd like to be able to link the monthly total cells in each month with the correct cell on the year end page. For example, if Kiri made $250 in credit card tips in August, I'd like that information zap from the cell on the August page to the August section on the total page.

Any body know how to do this?

Comments

[karmabreeze.livejournal.com]

Try the pivot table wizard...? I know just enough about Excel to really mess everything up.

What Version?

[jarlsberg71.livejournal.com]

Cause I do this with Backup Throughput ratess on my sheet at work. I can send it to you if you like I'm using office 2000. Each page gets a cell reference that is soemthing like A:B32 if it's the first page.... you should be able to copy and paste the cell. just paste special and do value only.

[pbrim.livejournal.com]

You can use a cell on another sheet in a formula just the same as you can another cell on the same sheet by appending the sheet name at the start of the address with a ! separator. For example, if the total credit card tips is in cell D25 on sheet August, then in the appropriate cell of the Year End sheet the formula would be =August!D25. If there is a space or other special character in the name of the sheet, you will need to put the sheet name in quotes (frex "Table 1"!A1).

A simpler way to do this is to use the browse method: just press = to get the formula started, then use the mouse or Ctrl+PageUp/Ctrl+PageDn to get to the right page and the mouse or arrow keys to get to the right cell, then press enter. If you are entering/editing a formula, F2 toggles between move-around-in-the-formula mode and move-around-in-the-workbook-to-insert-reference modes. (I hope that's clear.)

You can even use a cell in another workbook in a formula. The format is [Workbook]Sheet!A1. (The browse method works here too. Just open both workbooks at the same time and go to the Window dropdown menu to switch from one to another.) Thereafter, when you open the workbook, Excel will tell you it's linked to another workbook and ask if it should go update with current values, or just use the value it has. That way you could create a workbook for each year and a multi-year book that would pull in values for each of the other books for comparison.

[gridlore.livejournal.com]

Thanks!