Can Excel evaluate one cell and return the information in another?

J

John

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
 
W

wav0525

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

[email protected]
[email protected] with @tiscali.co.uk









- Show quoted text -
Click to expand...

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

wav0525

[email protected]> wrote in message



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

[email protected]
[email protected] with @tiscali.co.uk







- Show quoted text -
Click to expand...

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
 
S

Sandy Mann

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
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
 
W

wav0525

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
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
Click to expand...

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

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk


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
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
Click to expand...

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....
Click to expand...
 
Top