Excel =IF

I have attaqched a spread sheet example.

I wiould like sheet one to equal sheet two if the dates are between 1-1-2015 and 12-31-2015 using a basic =if statement.
Experts2.xlsx
Curtis LongAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Use this code...It will do what you are looking for...

Sub getdata()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lr As Long, lr1 As Long
Dim rng As Range, cell As Range

Set ws = Sheets("Sheet1")
Set ws1 = Sheets("Sheet2")

lr = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row

If lr > 1 Then ws.Range("A2:B" & lr).ClearContents

lr1 = ws1.Cells(Cells.Rows.Count, "a").End(xlUp).Row

Set rng = ws1.Range("B2:B" & lr1)

For Each cell In rng

If Year(cell.Value) = 2015 Then cell.EntireRow.Copy ws.Range("A" & ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1)
Next cell

MsgBox "Done"

End Sub

Open in new window


Saurabh...
0
 
Santosh GuptaCommented:
do u mean if sheet 1 A2 matchs in sheet2 then date value returns ??
0
 
Saurabh Singh TeotiaCommented:
Apply this formula...

=IFERROR(IF(AND(VLOOKUP(A2,Sheet2!A1:B5,2,0)>=DATE(2015,1,1),VLOOKUP(A2,Sheet2!A1:B5,2,0)<=DATE(2015,12,31)),VLOOKUP(A2,Sheet2!A1:B5,2,0),""),"")

Open in new window


Saurabh...
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
mark_harris231Commented:
To clarify your request, you would like Sheet1 Name and Date to be populated only for those entries on Sheet2 that meet your date criteria?  (i.e., Bob and Mike are not represented on Sheet1 because the Dates do not meet the criteria)

Or, will the names be populated on Sheet1, but not the date unless it meets the criteria?  (i.e., Bob and Mike may appear on Sheet1, but no date since it does not meet criteria)
0
 
Curtis LongAuthor Commented:
Yes mark, that is EXACTLY what I want.  :-)
0
 
Santosh GuptaCommented:
Ok, Then you should use vlookup instead of IF function.

=VLOOKUP(A2,Sheet2.$A$2:$B$5,2,0)


I hope it will help you.
0
 
dlb6597Commented:
This will populate the sheet 1 column B date field for each user if the Year of the date value in sheet 2 column B is 2015. This assumes both date columns are formatted as the date type...

=IF(YEAR(VLOOKUP(A2,Sheet2!A:B,2,FALSE))=2015,VLOOKUP(A2,Sheet2!A:B,2,FALSE))
0
 
Curtis LongAuthor Commented:
These all seem to bring just a date.  How about if I wanted just the name if the date was between 1-1 and 12-31 2015??
0
 
Santosh GuptaCommented:
you can also try "Custom Filter" and then select "is greater than or equal to" as first condition and then "is less than or equal to" as second condition.

filter
0
 
Rob HensonFinance AnalystCommented:
Alternative would be to use Advanced Filter.

You create a small table with your criteria made up of the heading(s) from the columns that you want to query ie the date column in your case and build the criteria in cells below the header. For your range of dates you would repeat the header column with a formula below each:

Formula 1
="=>"&"01/01/2015"

Formula 2
="<="&"31/12/2015"

If you need more help with Adv Filter, let us know.

Thanks
Rob H
0
 
mark_harris231Commented:
Purely as an exercise in formula tinkering, check the following.  This can be done by formula with some caveats:
Copy-of-Experts2.xlsx
- Addition of a "helper" column in Column C (which could be hidden)
- Sheet2 sorted by date Newest to Oldest
- "Starter" formula in A2 different from subsequent formulas in A3 and beyond
- Formulas in A3:C3 dragged down as far as needed

That said, this type of scenario is best handled in VBA as per Saurabh's post.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.