Spreadsheet Formating/layout?

[jackie77]

Hi all

I am new to excel and I was looking for some help, I have a spreadsheet with a list of Job Id and address etc (exported from access) and am now looking to arrange the spreadsheet, I need it to divide the rows into sets of 50 ideally all held on one page, with a numer of running calculations placed at the bottom of each page. At the end of the document I would like it to carry out some final calculation. I am not sure if this is possible or even hope to go about it

I would greatly appricate any sugeestions to get me started

Many Thanks

Jackie

 

[qafself]

Hi Jackie,

Its often a good idea to have any summary calculations a the top of the sheet so that if extra rows are added any subsequent calculations don't get messed up.

It sound like it might be an idea to have each 50 rows in a separate sheet. If you do this and have your summaries at the top of each sheet it is easy to get aggregated totals in a summary sheet e.g. SUM(firstshet:lastsheet!A2) will add all the cells A2 into one summary cell.

If you post a copy of what you want to achieve it would be easier to advise you further.

Ed

 

[jackie77]

Hi Ed, thanks for the reply

I have attached a brief example of the kind of info that will be exported over from access, however it will include on average a thousand records/rows. what I am looking for it to to is automatically sort them rows into groups of 50after each 50 rows calculate for example:

Invoice 1 (first group of 50)
Total number of jobs in entire document i.e. 1000
Total jobs serviced (count 'serviced' row E)
Total to date serviced
Total that require work (Count in Rows I & K 'True' however only count 1 value per row)
Total to date requiring work

Invoice 2 (next 50)
Total number of jobs in entire document i.e. 1000
Total jobs serviced (count 'serviced' row E)
Total to date serviced (count nimber serviced on previous 50 + this 50)
Total that require work (Count in Rows I & K 'True' however only count 1 value per row)
Total to date requiring work (count number requiring work on previous 50 + this 50)

and so on -------

At the end I would like a final summary of
Total number of jobs in entire document i.e. 1000
Total jobs serviced
Total that require work

I don't mind if they are on separate sheets but not sure how to when I paste them in they automatically divide up into 50 over separate sheets

I would ideally like them to format into this template during the export from access but as my experience is limited i am not sure if I will be able to manage it

Any ideas of where I should start?

Jackie

 

[qafself]

Hi Jackie,

See the attached

I have shown how you could split them up, 50 per page and how you can do the more difficult summaries - you may need to adjust the ranges if you do it differently but it should give you some ideas.

Ed

 

[jackie77]

Many many many thanks for this, its great, it would have taken me ages to get this far, so many thanks for the help it really is most appricated.
your great !!!


It looks like its exactly what I need, So what I do now is just paste the entire 1000 or so rows into the report query sheet and it will divid them out? would it be possible to export to this querysheet directly from access?

Jackie

Jackie

 

[qafself]

Hi,

Yes, you have two options.

One is to do all the work in the query report and do the summary every 50 rows, the alternative is to just dump the information in the queryreport and then create the other sheets to break it down into.

can you export dirrctly from access?

Probably, but I'm not an access expert so cant give you a lot of help. You could certainly do it as a csv and then import that directly into excel.

Ed