Newbie questions on spreadsheet

[WayneC]

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

 

[Tom Ogilvy]

1.

Is there a simpler approach?

Propogate to the right

2.
No.

 

[WayneC]

1.


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

 

[Gord Dibben]

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

 

[Sandy Mann]

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

 

[WayneC]

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