Excel - extract data from multiple worksheets

[Omono]

I have a financial expendures workbook. There is a worksheet for each month. Each monthly worksheet has the following columns; date, description of expendure (normally a single word) and the dollar amount spent. The items are paid at different times during the month so the description and amount is not in the same cell on every worksheet - they are tracked by date paid. I want to search all worksheets, by description, and pull the amount spent for each item per month to a new worksheet so the average can be
computed. For example; find all instances of "PHONE"; copy the amounts, which will be in the cell immediately right of the descriptions, to a new worksheet. Of course there would be a list of descriptive words to search for and retrieve the data. Is it possible? Any suggestions?
Thanks,
O'Neal Freeman

 

[DaleWatson123321]

Hi O'Neal,

Excel has a VERY powerful set of "database functions" that would be QUITE suitable for your situation.

If your data is already reasonably consistent in the way the data is identified, then using the function =DSUM and/or =DAVG will generate the exact results you're seeking. You can specify "criteria" for these formulas that can include a "variety" of different types of entries.

However, if your data is somewhat inconsistent - for example "Phone" entries have been entered in several different ways, and the same for other types of entries, then you perhaps should consider inserting an additional field. This field would be used to enter a specific "identifier" for each type of category you need to summarize by. From your description, it sounds like your data is fairly consistent, so you probably will NOT need to use this separate field/identifier.

Another possibility to consider, is setting up ONE database - i.e. ONE sheet with ALL your data (for one year). This can only be possible, however, if your total records for the year will NOT exceed Excel's maximum number of rows (65,536). Then, using these same database functions ( =DSUM or =DAVG ) you can produce monthly reports for ANY month by simply entering the number of the month in one cell.

Specifically, entering the number "5" will instantly produce your monthly report for "May". Then simply entering "6" will do the same for "June", etc.

You could even utilize a "CHOOSE" function, to allow you to use this ONE set of summary formulas to choose between TOTALS and AVERAGE.

Unfortunately, there is not too much help available on the use of Excel's database functions, AND there are a couple of minor BUGS in the form of FALSE ERROR messages you might encounter. One says you can't extract data to a separate sheet, and the other says you can't place your "criteria" on a separate sheet. Both of these ARE possible.

If you decide you want to pursue this option, I would be prepared to help "get you started".

The preferable route would be for you to email me your file. If sensitive data is a concern, replace it with fictitious data that still reflects the type of data your dealing with. If file size is a concern, perhaps only ONE month will be adequate. And I don't need a whole month of data, but data for each category would help.

If you have already determined the category names and the type of data that falls into each category, definitely include this information.

Hope this is seen as a worthwhile option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dwilson01]

This seems to do what you need, however, there is one thing you need to have set up in your workbook for this code to work as is, that is a sheet named "Total". One additional thing to be aware of is that the Total sheet will be appended with this code, meaning, if you run multiple filters (i.e. Phone, Air fair, Hotel) the data will be added to the next empty row. Anyway, here it is, hope this helps.


Dim txtSearch As String
Dim sh As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
txtSearch = InputBox("Enter Description of Expense search string&quot
For Each sh In wb.Worksheets
If sh.Name = "Total" Then GoTo nextsheet
With sh.UsedRange
Debug.Print sh.Name
Set c = sh.UsedRange.Find(txtSearch)
firstfind = c.Address
Do
c.EntireRow.Copy Destination:= _
Sheets("Total&quot.Rows(Sheets("Total&quot.UsedRange.Rows.Count + 1)
Set c = sh.UsedRange.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstfind
End With
nextsheet:
Next

Dave