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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.