Collecting From Multiple Sheets to One Spreadsheet

Hello Guys: Need Some help

I have a spreadsheet that I use every week, new spreadsheets are created each week when I have generated time sheet and invoice etc.

I want to be able to collect certain data from these spreadsheet in to one spreadsheet for accounting purposes, total VAT, Total expenses etc. so I can submit just one form of data to accountant per month or even per year.

At the moment I have in one folder about 20 spreadsheets relating to last 20 weeks and many more to come in the forthcoming weeks until the the end of financial year. so we have definite names of spreadsheet as well as wildcard names for those that are not in folder yet.

Can anyone help ?

Regards

ASH

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, or as in your case splitting basic data across many sheets and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.

You will also throw open the whole wonderful world of the powerful Pivot table functionality which will allow you to analyse in ways you've not yet thought of.

I suggest you put all your weekly data on a single sheet as a simple table of data. You'll need an additional column to include a reference to the week in question. I suggest you use the week ending date and enter the appropriate date on each record row.

Difficult to advise further without seeing the workbook.

Hi,

Difficult to comment since I don;t understand why all the sheets are needed. Your Invoice seems to based on the data on the Formula sheet and not the Input sheet, which is sort of counter-intuitive and confusing. In addition presumably the Formula sheet is meant for one particular employee and there will be many of these. However the 'Name' column has days in it, which again is confusing.

You need a single input sheet with columns for all the information that you want to capture. Typically these will be Name, Date, Start, LunchOut, LunchIn, End,...etc.

Create a new row every time you want to record an employee record so that you have a simple 2D table with no subtotals or other extraneous information. When you've done that, post back saying how the invoice should be summarised using the new table.

Hi,

When you say 'collected' do you mean that each time you produce an invoice and say click a button, you want the net & gross to be listed on a new row in the summary sheet?

And is it just net & gross? Don't you also require the Inv #, date, Customer, .... etc.?

Hi,

I'm confused: again.

Previously I thought we were dealing with a single workbook, with a summary sheet so that each time you clicked a 'Save Invoice' button, the invoice would be added to the summary sheet in the same workbook.

Now you seem to be suggesting that you have several separate workbook files, ...and what? You want the invoice details in each file to be added to a Summary sheet in the same workbook as the invoice, or in a single master workbook which summarises information from many workbooks?