We help IT Professionals succeed at work.

Excel =IF

Curtis Long
Curtis Long asked
on
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
Comment
Watch Question

Top Expert 2014

Commented:
do u mean if sheet 1 A2 matchs in sheet2 then date value returns ??
Top Expert 2015

Commented:
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...
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)

Author

Commented:
Yes mark, that is EXACTLY what I want.  :-)
Top Expert 2014

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

Commented:
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))

Author

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??
Top Expert 2015
Commented:
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...
Top Expert 2014

Commented:
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
Rob HensonFinance Analyst

Commented:
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
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.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.