Technical Discussion
  >> Windows Issues

Register (or login) on our website and you will not see this ad.

  Print Thread
Standard User camieabz
(sensei) Sat 08-Feb-14 00:00:48
Print Post

Excel - Planning a large spreadsheet - Ideas

[link to this post]
I have a large spreadsheet which records lots of data on a weekly basis. I plan to retire said spreadsheet at the end of the year, and as the existing one came from nothing and got built as necessary, I thought I'd start planning the new one.

For example, the way some of the data is presented, it sometimes makes sense to have dates heading the columns of data as entered, but might there be advantages to putting the dates in rows, and all data along the columns on the same row?

Much of the data will end up within charts, or will contribute to charts along the way. From time to time I have to pull selected data and create a chart of range of dates, and sometimes I have to transpose the data. So you see my thinking. I'm trying to anticipate future issues, such as "you should have done the dates along the rows, instead of the columns". smile

Of course if there's a simple way to get one sheet to mirror another with the data transposed as entered, I can refer to either as I wish. Not sure if that's really straight-forward though.

Not looking for the bet way. Just looking for others' opinions and experiences, and perhaps a few reasons to go down one route over the other. There's nothing too complicated happening in it really. Averaging, offsets, some IF formulae. INDEX and LOOKUP forms a fair bit of a 'master sheet' that pulls a lot of recent data to another sheet. MIN, MAX, and CONCATENATE too. All that sort of thing.

Some permanent cells will be created for reference, such TODAY() for when a chart is created, so the date is the date of publishing and so on. There are consistent headers across many of the charts (e.g. Sales - Tom, Dick Harry...Sales - Hope, Faith Charity), so there might be scope to have macros for quick pasting of column or row headers.

Then I have another SS that consists of charts, and it takes data from the first to populate. Then I run a macro and get the charts as bitmap images. All quite automated. Then converted to png and optimised and uploaded. 128 chart images in about 2 minutes. The 'post-excel processes are pretty slick, so that's less of an issue.

So as I said, the original was made over a period of time, with bits added as I thought of new things to add. I want this one to be more simple on a day to day basis, and want to start off properly.

What I really lack is experience of forward planning of spreadsheets (it might even qualify for project management, although there's no budget and no time scale, other than before 2015). I tend to create them from the ground, up. So I'm wondering if there's a method, or a way to go at this, or should I just do it.

All sensible idea welcome (in case someone suggests something silly).
  Print Thread

Jump to