On Mar 26, 4:28 am, "Sandy Mann" wrote:
wrote in message

oups.com...



Waht I mean is , I keep track of 4 seperate totals by month for 15
locations Example: Cost of goods, net sales etc.. what I would like is
that when I imput those sepeate spreadsheets, I can choose from a drop
down list the month and then it will give me the info for those
different imputs in one spreadsheet by month..Hope that is clear

Do you want formulas with just the totals for a month?
If so assuming that:
1. Headers in all data sheets in Row 1 and Dates in column A
2. You have a consolidation sheet named "Record"
3. In Sheet("Record"):
Cells A1:A12 have a list of January - December
Cell B1 has the formula: =MATCH(C1,A1:A12,0)
Cell C1 has the dropdown referencing the list in A1:A12
(Columns A:B can be hidden if you want)
4. The figures to be added are in Column E

Then try something like:
=SUMPRODUCT((MONTH('Location One'!A2:A366)=B1)*('Location One'!E2:E366))

If you mean you want the all the data for the relevant month in another
sheet then assuming the above set up, I recorded and adapted the following
code which copies all data for the selected month into the "Record" sheet:

Option Explicit
Sub CopyIt()

Dim tSheet As Worksheet
Dim aMonth As Long
Dim rRecordEnd As Long
Dim tEndData As Long
Dim rEndData As Long
Dim rSheetEnd As Long
Dim r As Worksheet

Set r = Sheets("Record")

Application.ScreenUpdating = False

rRecordEnd = r.Cells(r.Rows.Count, 5).End(xlUp).Row
r.Range(r.Cells(1, 5), r.Cells(rRecordEnd, 256)).Clear

For Each tSheet In Worksheets

aMonth = r.Cells(1, 2).Value
rRecordEnd = r.Cells(r.Rows.Count, 5).End(xlUp).Row
If rRecordEnd 1 Then rRecordEnd = rRecordEnd + 2

tSheet.Activate
If tSheet.Name = Sheets("Record").Name Then GoTo skip

tEndData = Cells(Rows.Count, 1).End(xlUp).Row
rEndData = Cells(1, Columns.Count).End(xlToLeft).Column

Columns("A:A").Insert Shift:=xlToRight
Range("A2").FormulaR1C1 = "=MONTH(RC[1])"

Range("A2").AutoFill Destination:= _
Range(Cells(2, 1), Cells(tEndData, 1))

Range("B2").AutoFilter Field:=1, Criteria1:=aMonth
Columns("A:A").EntireColumn.Hidden = True
Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2). _
CurrentRegion.Copy Destination:= _
r.Cells(rRecordEnd, 5)

r.Cells(rRecordEnd, 4).Value = tSheet.Name
Range("A1").AutoFilter
Columns("A:A").Delete Shift:=xlToLeft

Application.Goto reference:=Range("A1"), Scroll:=True
skip:
Next tSheet

r.Select
Application.ScreenUpdating = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Sandy,

Thanks, for your effort. The problem is that I am not well versed in
macros. I applied all the info you sent , but it doesn't work. Maybe
you would like to see the worksheet? Then you will know what I would
like...If not, I completely understand, and I thank you so much....