I am trying to have excel evaluate one cell for a particular month and return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John

One way:

Put this in A1:

=IF(B2="June", "Thompson","")

In article ,
John wrote:

I am trying to have excel evaluate one cell for a particular month and return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John

John,

=IF(B2="June",A1,"")

If by chance B2 contains an Excel date then use:

=IF(MONTH(B2)=6,A1,"")

--
HTH

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


with @tiscali.co.uk


"John" wrote in message
...
I am trying to have excel evaluate one cell for a particular month and
return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John

On Mar 24, 6:15 am, "Sandy Mann" wrote:
John,

=IF(B2="June",A1,"")

If by chance B2 contains an Excel date then use:

=IF(MONTH(B2)=6,A1,"")

--
HTH

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


with @tiscali.co.uk

"John" wrote in message

...



I am trying to have excel evaluate one cell for a particular month and
return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John- Hide quoted text -

- Show quoted text -

What if you have 12 months (Jan-dec) how do you pick the particular
month to show data for that month only?

wrote in message
oups.com...

What if you have 12 months (Jan-dec) how do you pick the particular
month to show data for that month only?

I don't understand what you mean. Can you elaborate, In what form is the
Jan - Dec, where is the repated data?


--


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


with @tiscali.co.uk


wrote in message
oups.com...
On Mar 24, 6:15 am, "Sandy Mann" wrote:
John,

=IF(B2="June",A1,"")

If by chance B2 contains an Excel date then use:

=IF(MONTH(B2)=6,A1,"")

--
HTH

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


with @tiscali.co.uk

"John" wrote in message

...



I am trying to have excel evaluate one cell for a particular month and
return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a
name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John- Hide quoted text -

- Show quoted text -

What if you have 12 months (Jan-dec) how do you pick the particular
month to show data for that month only?

On Mar 25, 9:00 am, "Sandy Mann" wrote:
wrote in message

oups.com...

What if you have 12 months (Jan-dec) how do you pick the particular
month to show data for that month only?

I don't understand what you mean. Can you elaborate, In what form is the
Jan - Dec, where is the repated data?

--

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


with @tiscali.co.uk

wrote in message

oups.com...



On Mar 24, 6:15 am, "Sandy Mann" wrote:
John,

=IF(B2="June",A1,"")

If by chance B2 contains an Excel date then use:

=IF(MONTH(B2)=6,A1,"")

--
HTH

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


with @tiscali.co.uk

"John" wrote in message

...

I am trying to have excel evaluate one cell for a particular month and
return
the information found in a seperate cell if it is true. IE.

If B2 = "June", then return the information in A1 (which would be a
name
"Thompson"). Also if it does not say "June" then it leaves it blank.

Thompson
Capt I Blue
ER Due June

Thank you
John- Hide quoted text -

- Show quoted text -

What if you have 12 months (Jan-dec) how do you pick the particular
month to show data for that month only?- Hide quoted text -

- Show quoted text -

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

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

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....

Yes certainly send the sheet if it is not too big. Just replace the part of
my address after the @ sign as it says in my signature.

--

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


with @tiscali.co.uk


wrote in message
oups.com...
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....