Excel - Comparing two cells from different spreadsheets
Consider the following situation:
Spreadsheet A
Sheet1
Name Expenses Date Project
John 10 23/10/2011 (Get data from Spreadsheet B)
Mark 50 12/5/2011 (Get data from Spreadsheet B)
Patricia 34 4/11/2011 (Get data from Spreadsheet B)
Anthony 88 19/3/2011 (Get data from Spreadsheet B)
Mark 20 5/7/2011 (Get data from Spreadsheet B)
Spreadsheet B
Sheet1
Name Project Date
Mark Painting 12/5/2011
Anthony Singing 19/3/2011
Patricia Teaching 4/11/2011
Mark Reading 5/7/2011
John Painting 23/10/2011
What I need is to get the name of the project from Spreadsheet B (sheet1) and add to the column PROJECT in Spreadsheet A (sheet1). How to automate this process?
Try this
Sample Call :
=projectLookup(A2,C2)
A2= Cell in which name is found
C2= cell in which date is found
Sheet1: name where the information about project is present
Option Explicit
Public Function projectLookup(nameCell As Range, dateCell As Range) As String
Dim sourceSheet As String
Dim nameRow As Long
Dim maxRows As Long
sourceSheet = "Sheet1"
projectLookup = vbNullString
With Sheets(sourceSheet)
maxRows = getItemLocation("*", .Cells)
If maxRows = 0 Then Exit Function
nameRow = 1
Do
nameRow = getItemLocation(nameCell.Value, .Range(.Cells(nameRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
If (nameRow > 0) Then
If (.Cells(nameRow, "C") = dateCell) Then
projectLookup = .Cells(nameRow, "B")
nameRow = 0
ElseIf (nameRow = maxRows) Then
nameRow = 0
Else
nameRow = nameRow + 1
End If
End If
Loop While (nameRow <> 0)
End With
End Function
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
Thanks to rizvisa1 for this tip.
Related
- Excel - Comparing two cells from different spreadsheets
- Excel compare two cells for similar text - Guide
- Comparing two cell in different spreadsheets ✓ - Forum - Excel
- Excel formula to compare two cells and return a value ✓ - Forum - Excel
- How to compare two cell values in excel using macro ✓ - Forum - Excel
- Excel formula to compare two cells and return a value true or false ✓ - Forum - Excel
This document, titled « Excel - Comparing two cells from different spreadsheets », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).