My workbook is getting too big!

[Landmark]

Hi, anyone any thoughts on how I can best manage a very large
forecasting workbook with several spreasheets all linking in to one
another.

I already use a specialist program (Sage financial foreacsting) to
manage my high level forecasts, but for the real low level cost
management I do a certain amount of preparation in excel, hence the big
workbook.

Main problem is introducing changes as and when they happen, everytime
I need to change the spreadsheet it involves an enormous amount of
work, and of course if I dont remember to update in all the right
places the whole thing goes caput.

I wondered if there was some sort of clever way of introducing some
sort of input screen so that whenever "inputs" change, you only have to
format one set of formulas and one set variables etc..

I dont know if I am explaining myself very well, but I would be very
interested if anyone could suggest anything to make this process less
cumbersome.

Thanks.

 

[Guest]

... Main problem is introducing changes as and when they happen, everytime
I need to change the spreadsheet it involves an enormous amount of
work, and of course if I dont remember to update in all the right
places the whole thing goes caput.

You should never have to remember all the right places to update - it is the
job of computers to remember. Your workbook should be set up so that
inputs/changes need to be made in only *one* place, and should be propagated
with formula references everywhere they need to be used. That can be done
across multiple worksheets in a workbook (and even across multiple workbooks
- although this is not recommended).

 

[Landmark]

Hi Ted, I do agree with you about automating the sheet as much as
possible, but most of the things I am changing are formatting things..
for example, lets say the company have decided to create three more
nominal codes, it means I need to go and insert three more lines at the
bottom of each one of my groups on each worksheet and copy down all the
formulas. I suppose the clever way of me dealing with this is to leave
enough spare lines in the first place for this sort of occasion !!!

I am still not sure I have created the most user friendly workbook, it
is OK, but it is just so big and anything of that size needs careful
managing. I am debating whether I should just set up a new company on
sage financial forecasting for each one of my "projects" as I feel
safer having all the info stored in a database, however, the reason I
created it in excel was so that I could easily run graphs and pivots
off the data.

What would be really cool is if I could use some sort of form to enter
the data, rather than keying it directly into the excel workbook, which
would then be stored in columns in excel which I could then use to run
my graphs and pivots, this is just me thinking aloud by the way, I am
probably making no sense at all!!

 

[Shhhh]

That can be done
across multiple worksheets in a workbook (and even across multiple
workbooks
- although this is not recommended).

Just out of curiosity... why is that not recommended? I have a couple of
excel files that look at other files for information, haven't (knock on
wood) had any problems yet.

Thanks,
Shhhh