At 3/14/05 04:34 AM, Denvish wrote:
At 3/14/05 04:27 AM, D0GMA wrote:
meep moop miip
Chris, quick Excel question
If I want to duplicate a formula such as
C4=B4-B3
So it goes all the way down the C column, up to (eg) C644=B644-B643
Is there anyway to do that without having to do it individually for each C cell?
If there is, could you possibly give instructions in 'Excel Idiot' format?
There's two ways to do it.
If there is data in column B the entire way down , type the formula in C4 and hit Enter. Then uparrow to it and in the lower right hand corner of the cell highlight you'll see a crosshair. Double-click on it, and it will copy it down the entire way there is data in B.
A few caveats to that method:
If there is already data in Column C, it will stop where it does, then you can repeat the above to get it the rest of the way ... unless there are more line breaks, then you're better off with the "hard" way below.
If there is a line break at, say row 645 and you start up again with something else on 646, that column copy will put your formula in C645. It's not a problem now, but if you do that again, rows 644 and 646 are now connected by something in C645 and it will copy down through the subsequent section as well. I run into that a lot on the Submission Stats charting sheet.
If there is data in both B and D and line breaks in either (and no breaks or other stoppages in C to override this), if the breaks in one column are covered by data in the other, the entire thing is considered to be contiguous and it will copy all the way down.
The "hard" way to do it is to either right or left click on that crosshair and pull it all the way down. Left click is a straight full copy, formatting and all. Right-click will bring up a menu allowing you to choose how (and what) you want copied through.
Hidden rows will be included in the copy via either method, which is why I have to do the B and D (active only) Top 50 lists manually. Right-Click-and-Drag ---> Fill Series assigns rank numbers to the hidden cells as well. With what it sounds like you're doing, this shouldn't be an issue, but just another "so you know" kind of thing.
Not sure if you're familiar with this, but an extension to the copying method is when you want it to do something like always subtract B3. If each row is a total BP on a particular date and you want to keep a running tally of BPs since a given date instead of (or in addition to) the per day amounts you'll be yielding via your above copies, you designate a fixed cell component (row, column, or both) with $.
=B4-B$3 in C4 and copied down (or up), will be =B644-B$3 in cell C644.
=B4-$B3 in C4 and copied across (or back), will be =Z4-$B3 in cell Z4
=B4-$B$3 in C4 and copied in any direction, angle, or simply copied and pasted into an entirely new set of formulae you're creating locks B3 as the referent cell no matter where you put it.
You can also designate a different sheet, or sheet within another Excel file, as a reference by precursors to the actual cell designation.
=sheetname!B4
=[filename.xls]sheetname!B4
There used to be a designation for the filepath, if the other Excel was in a different directory, but XP and Office2k4 isn't showing any syntax for that.
You'll run into problems if you start renaming referenced pages, sheets, or moving things around into different directories, but sometimes it's useful to have data in Excel files partitioned from each other, particularly in a real office environment. The CFO may have spreadsheets that access data from expense reports. The users go to a base communal expense report Excel file (with some cells to which the user can make changes and the rest locked like I did with my NWN file) that each user then downloads, modifies, and uploads into a specific directory (say I:/Expense/2005/March/JSmith). The CFO then just modifies their spreadsheet to accommodate new users and times for whatever needs they have, and are also protected from anyone in the company being able to view that information.
