Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I've been a casual user of xcel for awhile, but haven't figured some
stuff out: 1. I have a spreadsheet that needs to expand by inserting several columns near the left side each year; ie, there are a couple of columns of text for various accounting categories/items on the left, then I want to have 2 or 3 columns representing values and calculations for the current year (some of which depend on using prior year numbers for calculations) then columns for the prior year, then the next prior year,etc, etc. I find it very difficult to update the spreadsheet each year, since simply inserting new columns and copying the prior year columns and/or dragging/propagating the prior year cells to the left doesn't seem to result in correct formulas (as they might if I propagate to the right) so I have to change just about every cell manually. It's a very long spreadsheet. Is there a simpler approach? 2. Related question: Is there a method of sectioning off a spreadsheet, such that you can have text at the top, some rows & columns of constants and calculated cells, a section of different text, a differently-divided section of rows & columns (different than the prior section), etc.... and, when columns are added to one section it does not affect the other sections (doesn't divide the text sections nor affect columns in another section)? In other words, like 4 (or more) different spreadsheets all in one for perusal/printing? Wayne |
#2
![]() |
|||
|
|||
![]()
1.
Is there a simpler approach? Propogate to the right 2. No. -- Regards, Tom Ogilvy "WayneC" wrote in message ... I've been a casual user of xcel for awhile, but haven't figured some stuff out: 1. I have a spreadsheet that needs to expand by inserting several columns near the left side each year; ie, there are a couple of columns of text for various accounting categories/items on the left, then I want to have 2 or 3 columns representing values and calculations for the current year (some of which depend on using prior year numbers for calculations) then columns for the prior year, then the next prior year,etc, etc. I find it very difficult to update the spreadsheet each year, since simply inserting new columns and copying the prior year columns and/or dragging/propagating the prior year cells to the left doesn't seem to result in correct formulas (as they might if I propagate to the right) so I have to change just about every cell manually. It's a very long spreadsheet. Is there a simpler approach? 2. Related question: Is there a method of sectioning off a spreadsheet, such that you can have text at the top, some rows & columns of constants and calculated cells, a section of different text, a differently-divided section of rows & columns (different than the prior section), etc.... and, when columns are added to one section it does not affect the other sections (doesn't divide the text sections nor affect columns in another section)? In other words, like 4 (or more) different spreadsheets all in one for perusal/printing? Wayne |
#3
![]() |
|||
|
|||
![]()
Tom Ogilvy wrote:
1. Is there a simpler approach? Propogate to the right Well, yeah, but... I want the most recent data on the left next to the text, where I can read it, not off the page to the right of numerous other years of data. 2. No. Dang, that seems like a thing a lot of people would want to do |
#4
![]() |
|||
|
|||
![]()
Well, yeah, but... I want the most recent data on the left next to the
text, where I can read it, not off the page to the right of numerous other years of data. Hide the columns you don't want to see to the left of newest data. Gord Dibben Excel MVP On Mon, 20 Dec 2004 13:06:02 -0800, WayneC wrote: Tom Ogilvy wrote: 1. Is there a simpler approach? Propogate to the right 2. No. Dang, that seems like a thing a lot of people would want to do |
#5
![]() |
|||
|
|||
![]()
Well, yeah, but... I want the most recent data on the left next to the
text, where I can read it, not off the page to the right of numerous other years of data. I may be misunderstanding it but isn't that what Tom is suggesting? Select the cell that you want to enter the data into, ie the cell next to your text, then select Insert Cells Shift Cells Right A space will open up in that row for you to insert your new data HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "WayneC" wrote in message ... Tom Ogilvy wrote: 1. Is there a simpler approach? Propogate to the right Well, yeah, but... I want the most recent data on the left next to the text, where I can read it, not off the page to the right of numerous other years of data. 2. No. Dang, that seems like a thing a lot of people would want to do |
#6
![]() |
|||
|
|||
![]()
Sandy Mann wrote:
Well, yeah, but... I want the most recent data on the left next to the text, where I can read it, not off the page to the right of numerous other years of data. I may be misunderstanding it but isn't that what Tom is suggesting? Select the cell that you want to enter the data into, ie the cell next to your text, then select Insert Cells Shift Cells Right A space will open up in that row for you to insert your new data HTH Sandy Well, I guess it turns out I didn't understand my own question. I haven't tackled alterations to the spreadsheet for so long I've forgotten. My recollection is that when I did make changes, by adding columns, it really messed up the spreadsheet formulas and got awfully tedious; but in playing around with a simplistic example spreadsheet getting ready for this reply, I did discover a method that might work, at least partially. I need to try it on my real spreadsheet. Thinking out loud (for anyone that cares to consider the issue).... My real spreadsheet is divided into sections. Some sections sum up figures in the other sections after doing additional calculations on the numbers. To explain what each section is/does, there is verbiage in several rows that precede the section. In general, the first few sections sum up data that occurs in multiple sections further down in the spreadsheet, and each section starts with a row or two that sums up the data within that section. For simplicity's sake, consider this example: Assume I have numbers in a detail section starting on row 7 as follows: Assume rows 7 & 8 contain column header constants ("2004", "2003" etc For the rows that follow (say 10 through 20): A & B: category headers and other fixed data pertaining to the row (perhaps something like various budget categories of expenditures). C, E, G, I, K, M, etc are numerical values for a given year, where 2004 data is updated periodically to show progress/change. D shows the percentage difference (plus/minus) between E and C ... ie, formula "=(C10-E10)/E10" Likewise F shows the gain/loss % between G and E, etc I have/want the data in descending order by year across the spreadsheet; ie: 2004 (C & D), 2003 (E & F) etc, so I can see the current year data adjacent to the category header text, and can easily scan to the right to compare the data to prior years in descending order. Note: I suppose I could arrange the years in ascending sequence, put all the category headers at the extreme right, and simply hide old year columns at the left to format one pagewidth for printing, but reading right to left just doesn't come natural. Above that whole section is a few rows filled with an explanation of what the data represents and what assumptions were used in coming up with the numbers and formulas, and a few constant values used in formulas; let's assume the explanation text covers A5 through J8. The problems come when 2 new columns are inserted on the left for 2005 data: 1. the columns with formulas don't propagate properly, due (I guess) to the intervening columns of data... doesn't matter whether they are propagated right or left... this a serious issue for me because the formulas are not nearly as simple as I stated here, requiring me to relearn all the old formulas and correct or enter them by hand; the spreadsheet is several hundred rows in length, consisting of multiple sections. The numbers in some sections are calculations that depend on calculations made in other sections. Would be nice if there were a simpler approach. 2. the explanation text in rows A5 through J8 suddenly has a hole down through it (not a serious issue, but an annoyance) 3. There are a few sections where I want the columns to represent things other than the year columns in preceding sections, and in different column widths, so it would be nice to have the ability to change the column configuration within sections of the spreadsheet (something Tom has already said cannot be done). Note on problem 1: But I did find a simpler approach to the example as I fooled with it: I inserted 2 columns to the left of C and did an edit copy of the old C and D columns to the inserted columns as a pair, so maybe problem 1 above (the formula manipulation for added columns) isn't going to be as bad as I thought. Wayne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Importing Data From Another Spreadsheet | Excel Discussion (Misc queries) | |||
How do I convert exel spreadsheet to works spreadsheet? | Excel Discussion (Misc queries) | |||
Updating 1 spreadsheet from another | Excel Worksheet Functions | |||
newbie questions | Excel Worksheet Functions | |||
How do I cancel sending a spreadsheet by email? | Excel Discussion (Misc queries) |