Excel 2013: I need Sheet1 Col D = Sheet2 Col D IF Sheet1 Col B = Sheet 2 Col A , else Sheet1 Col D is yesterdays date

0
votes

Using Excel2013, I need to populate cells in Sheet1 Column D with entries in Sheet2 Column d only if the entry in Sheet1 Column B = Sheet2 Column A, otherwise Sheet1 Column D is populated with yesterday's date. This needs to refer to the sheet's index number, not the name or code name, as Sheet2 will change daily.

I am VERY new to VBA and really have no idea where to start with this!

EDIT: Sheet1 is the full list and Sheet2 is the list of daily exceptions, or just those who aren't reporting. So I need it to look at all of ColumnA and compare it with all of columnB. Sheet2 ColumnD is the last reporting date.

And being VERY new to this, all I've tried are rather basic formulas. If I didn't need it to always refer to the second sheet, it'd be done! I appreciate ALL suggestions!

2
vbaexcel
To refer to the first sheet in vba use Sheets(1) to refer to the second sheet in order use Sheets(2). The Sheets() can either take the index number which is the order of sheets, or it can be a string which is the sheet name. - Scott Craner
You don't need VBA for this. It's a simple IIF function call. - Ken White
@KenWhite - isn't IIf() only available in VBA? But OP, you're looking to do an If statement, by checking the sheet's index, Sheets("Sheet1").Index - BruceWayne
This LINK gives a quick UDF that can be referenced with INDIRECT, so you can do a simple IF() formula. - Scott Craner
@Bruce: Yes, my mistake. In the Excel formula, it's just IF() (one I). Change my last comment to It's a simple IF() function call. - Ken White

2 Answers

1
votes

Using the following UDF:

Function SHEETNME(number As Long) As String
    Application.Volatile
    SHEETNME = Sheets(number).Name
End Function

The UDF comes courtesy of THIS POST.

Paste this function in a module attached to the workbook. DO NOT put it in worksheet code or ThisWorkbook Code.

You then could use the following formula in D2 on Sheet1:

=IFERROR(VLOOKUP(B2,INDIRECT("'" & SHEETNME(2) &"'!A:D"),4,FALSE),TODAY()-1)

Then copy down

0
votes

Well, here is my answer, hoping to give some help.

If you want to use VBA:

Explanations, in the comments. 

Sub importaData()
    Dim r 'to record the last rows in column b in sheet1
    Dim B 'column number in sheet1
    Dim A 'column number in sheet2
    Dim sht1 As Worksheet 'to store the sheet1
    Dim sht2 As Worksheet 'to store the sheet2
    Dim i
    Dim n 'index
    Dim rngDsht2 As Range 'D column in sheet2
    Dim rngBsht1 As Range 'B column in sheet1
    Dim rngAsht2 As Range 'A column in sheet2
    Dim tmpB 'one cell of column B (of sheet1)
    Dim tmpD 'one cell of column D (OF SHEET2)!
    Dim tmpA 'one cell of column a (of sheet1)

    r = Range("B1").End(xlDown).Row 'As said, found the last row in column B
    B = 2 'Just the number of the columns
    A = 1 'Just the number of the columns

    Set sht1 = Sheets("Sheet1") 'Storing sheets objects into vars
    Set sht2 = Sheets("Sheet2")
    Set rngBsht1 = sht1.Range(Cells(1, 2), Cells(r, B)) 'the range of columns B with the data to compare
    'Set rngDsht1 = Range("B1" & Cells(r, B)) 'You can use this instead.
    sht2.Activate 'Go to sheet2 to set some ranges
    Set rngAsht2 = sht2.Range(Cells(1, 1), Cells(r, 1)) 'set the range of column A. "r" is the range of columns B in sheet1
    Set rngDsht2 = sht2.Range(Cells(1, 4), Cells(r, 4)) 'set the range of column D. "r" is the range of columns B in sheet1
    'Set rngDsht2 = sht2.Range("B1" & Cells(r, B)) 'You can use this instead.
    sht1.Activate 'Go back to sheet1
    n = 0 'Ini the var

    For Each i In rngBsht1 'For each cell into a range of column B
        n = n + 1 'increase the var by one every iteration
        tmpB = i.Value 'store the value of the cell
        tmpA = rngAsht2(i.Row, 1) 'store the value of the cell
        tmpD = rngDsht2(i.Row, 1) 'store the value of the cell

        If tmpB = tmpA Then 'make the comparison
            i.Offset(0, 2).Value = tmpD 'if equal, put the value of column D (sheet2) into cells of columns D in sheet 1
        Else
            i.Offset(0, 2).Value = Date - 1 'if not equal, put in column D sheet 1 the value of yesterday (the date)
        End If
    Next i
End Sub

If you want ot use formulas:

In D column in sheet1 you need to put this formula:

=IF(B1=Sheet2!A1,Sheet2!D1,TODAY()-1)

Summary

In sheet1:

Columns B: with data
Columns D: with no data, we need to put data into D column

In sheet2:
Columns A: With data, used to compare with data in column B in sheet1
Columns D: With data, to take, in case any cell in sheet1.ColumnB and sheet2.columnA were equal, and put into columnD in sheet1...