Re: Enter value 2 columns to right of cell address reference
Hi Everyone,
Here is my situation.
I really hope someone will be able to guide me in the right direction as spent like 2 days trying to workout the solution.
My Specification
O/S: Windows 8.1
Programs: Microsoft Excel 2013
Problem / Task
My very basic spreadsheet allows me to check the location of new email addresses entered to see if they already exist on that sheet followed by returning the cell reference. When it fails to find a match it returns "#N/A". As you can see my skills are limited when it comes to formulas in Excel.
I need Excel to check the cell reference found in "Column M" each time. As long as it returns a proper cell reference rather than "#N/A" we proceed by putting "U" in the very next column from that cell reference in the same row. As you can imagine it would prove a timely process to use "Find" facility on a sheet with 100,000 emails or having to scroll to that cell reference each time.
I would imagine to achieve this will probably require VBA coding although hopefully possible a formula?
Here is the setup
File: Master.xlsx
Sheet: Sheet3
Column A: ID
Column B: Email Address
Column C: Status
Column D: Details
Column E: Ref
Column F: Assigned
Column G: Type 2Column H: Type 3
Column I: Duplicate (Records entered here checked against column B for a match)
Column J: Cell Reference
Column K: Type 4 (Returns an "X" if match found in same row, to 2 columns to right of cell reference returned in Column I)
My spreadsheet is setup to return the location (cell reference) of an email address on the same sheet (sheet3).
Sample Data
[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl69, width: 64"]Email Address[/TD]
[TD="class: xl69, width: 64"]Status[/TD]
[TD="class: xl69, width: 64"]Details[/TD]
[TD="class: xl69, width: 64"]Ref[/TD]
[TD="class: xl69, width: 64"]Assigned[/TD]
[TD="class: xl69, width: 64"]Type 2[/TD]
[TD="class: xl69, width: 64"]Type 3[/TD]
[TD="class: xl69, width: 64"]Duplicate[/TD]
[TD="class: xl69, width: 64"]Cell Ref[/TD]
[TD="class: xl69, width: 64"]Type 4[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl69, width: 64"]Strong[/TD]
[TD="class: xl69, width: 64"]Science[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl66"]$C$3[/TD]
[TD="class: xl69, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl69, width: 64"]Medium[/TD]
[TD="class: xl69, width: 64"]IT[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl66"]$C$2[/TD]
[TD="class: xl69, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl67"]sample@test.com[/TD]
[TD="class: xl65"]Strong[/TD]
[TD="class: xl65"]IT[/TD]
[TD="class: xl65"]R12345[/TD]
[TD="class: xl65"]Pending[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl70"]test2@test.com[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Formulas
J2 is:
=CELL("address",INDEX($B:$I,MATCH(I2,$B:$B,0),2))
J3 is:
=CELL("address",INDEX($B:$I,MATCH(I3,$B:$B,0),2))
J4 is:
=CELL("address",INDEX($B:$I,MATCH(I4,$B:$B,0),2))
Expected Result
[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl69, width: 64"]Email Address[/TD]
[TD="class: xl69, width: 64"]Status[/TD]
[TD="class: xl69, width: 64"]Details[/TD]
[TD="class: xl69, width: 64"]Ref[/TD]
[TD="class: xl69, width: 64"]Assigned[/TD]
[TD="class: xl69, width: 64"]Type 2[/TD]
[TD="class: xl69, width: 64"]Type 3[/TD]
[TD="class: xl69, width: 64"]Duplicate[/TD]
[TD="class: xl69, width: 64"]Cell Ref[/TD]
[TD="class: xl69, width: 64"]Type 4[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl69, width: 64"]Strong[/TD]
[TD="class: xl69, width: 64"]Science[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl66"]$C$3[/TD]
[TD="class: xl69, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl69, width: 64"]Medium[/TD]
[TD="class: xl69, width: 64"]IT[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl66"]$C$2[/TD]
[TD="class: xl69, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl67"]sample@test.com[/TD]
[TD="class: xl65"]Strong[/TD]
[TD="class: xl65"]IT[/TD]
[TD="class: xl65"]R12345[/TD]
[TD="class: xl65"]Pending[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl70"]test2@test.com[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would be delighted to hear from anyone that can guide me in the right direction. A macro or just simple formulas will be fine with me. I still have this feeling it can be achieved with a formula, but will let you experts out there confirm whether that is possible.
Thank you so much in advance!
Best Wishes,
Chris
Hi Everyone,
Here is my situation.
I really hope someone will be able to guide me in the right direction as spent like 2 days trying to workout the solution.
My Specification
O/S: Windows 8.1
Programs: Microsoft Excel 2013
Problem / Task
My very basic spreadsheet allows me to check the location of new email addresses entered to see if they already exist on that sheet followed by returning the cell reference. When it fails to find a match it returns "#N/A". As you can see my skills are limited when it comes to formulas in Excel.
I need Excel to check the cell reference found in "Column M" each time. As long as it returns a proper cell reference rather than "#N/A" we proceed by putting "U" in the very next column from that cell reference in the same row. As you can imagine it would prove a timely process to use "Find" facility on a sheet with 100,000 emails or having to scroll to that cell reference each time.
I would imagine to achieve this will probably require VBA coding although hopefully possible a formula?
Here is the setup
File: Master.xlsx
Sheet: Sheet3
Column A: ID
Column B: Email Address
Column C: Status
Column D: Details
Column E: Ref
Column F: Assigned
Column G: Type 2Column H: Type 3
Column I: Duplicate (Records entered here checked against column B for a match)
Column J: Cell Reference
Column K: Type 4 (Returns an "X" if match found in same row, to 2 columns to right of cell reference returned in Column I)
My spreadsheet is setup to return the location (cell reference) of an email address on the same sheet (sheet3).
Sample Data
[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl69, width: 64"]Email Address[/TD]
[TD="class: xl69, width: 64"]Status[/TD]
[TD="class: xl69, width: 64"]Details[/TD]
[TD="class: xl69, width: 64"]Ref[/TD]
[TD="class: xl69, width: 64"]Assigned[/TD]
[TD="class: xl69, width: 64"]Type 2[/TD]
[TD="class: xl69, width: 64"]Type 3[/TD]
[TD="class: xl69, width: 64"]Duplicate[/TD]
[TD="class: xl69, width: 64"]Cell Ref[/TD]
[TD="class: xl69, width: 64"]Type 4[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl69, width: 64"]Strong[/TD]
[TD="class: xl69, width: 64"]Science[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl66"]$C$3[/TD]
[TD="class: xl69, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl69, width: 64"]Medium[/TD]
[TD="class: xl69, width: 64"]IT[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl66"]$C$2[/TD]
[TD="class: xl69, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl67"]sample@test.com[/TD]
[TD="class: xl65"]Strong[/TD]
[TD="class: xl65"]IT[/TD]
[TD="class: xl65"]R12345[/TD]
[TD="class: xl65"]Pending[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl70"]test2@test.com[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Formulas
J2 is:
=CELL("address",INDEX($B:$I,MATCH(I2,$B:$B,0),2))
J3 is:
=CELL("address",INDEX($B:$I,MATCH(I3,$B:$B,0),2))
J4 is:
=CELL("address",INDEX($B:$I,MATCH(I4,$B:$B,0),2))
Expected Result
[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl69, width: 64"]ID[/TD]
[TD="class: xl69, width: 64"]Email Address[/TD]
[TD="class: xl69, width: 64"]Status[/TD]
[TD="class: xl69, width: 64"]Details[/TD]
[TD="class: xl69, width: 64"]Ref[/TD]
[TD="class: xl69, width: 64"]Assigned[/TD]
[TD="class: xl69, width: 64"]Type 2[/TD]
[TD="class: xl69, width: 64"]Type 3[/TD]
[TD="class: xl69, width: 64"]Duplicate[/TD]
[TD="class: xl69, width: 64"]Cell Ref[/TD]
[TD="class: xl69, width: 64"]Type 4[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl69, width: 64"]Strong[/TD]
[TD="class: xl69, width: 64"]Science[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl66"]$C$3[/TD]
[TD="class: xl69, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, width: 64"]apple@test.com[/TD]
[TD="class: xl69, width: 64"]Medium[/TD]
[TD="class: xl69, width: 64"]IT[/TD]
[TD="class: xl69, width: 64"]None[/TD]
[TD="class: xl69, width: 64"]No[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl69, width: 64"]Yes[/TD]
[TD="class: xl68, width: 64"]test@test.com[/TD]
[TD="class: xl66"]$C$2[/TD]
[TD="class: xl69, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl67"]sample@test.com[/TD]
[TD="class: xl65"]Strong[/TD]
[TD="class: xl65"]IT[/TD]
[TD="class: xl65"]R12345[/TD]
[TD="class: xl65"]Pending[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl70"]test2@test.com[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would be delighted to hear from anyone that can guide me in the right direction. A macro or just simple formulas will be fine with me. I still have this feeling it can be achieved with a formula, but will let you experts out there confirm whether that is possible.
Thank you so much in advance!
Best Wishes,
Chris
Last edited: