Re: Enter value 2 columns to right of cell address reference

C

Chris2010

New Member
Joined
May 28, 2010
Messages
21
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
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Re: Enter value 2 columns to right of cell address reference

I don't see column M mentioned in your table, was that a mistake?

I need Excel to check the cell reference found in "Column M" each time.
Click to expand...

And what is the difference between these bottom two quotes?

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...
Click to expand...
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)
Click to expand...
 
Last edited:
Upvote 0
Re: Enter value 2 columns to right of cell address reference

Hi CyrusTheVirus,

I don't see column M mentioned in your table, was that a mistake?
Click to expand...

This was a mistake. It should have said "Column J".

And what is the difference between these bottom two quotes?
Click to expand...

Once again another mistake. There is no difference, it should say show "X" when a cell reference is found.

Sorry for the confusion.

Cheers,

Chris
 
Last edited:
Upvote 0
Re: Enter value 2 columns to right of cell address reference

Hi Chris,

So are you just wanting a formula for Column K or Column J, or both?

Do I understand you right that you are wanting a formula for Column K that populate an 'X' if there is a cell reference found in the cell to the left of it, but you don't want anything to populate if there is an error to the left of it?

If so, then maybe the below code could work?

Code: 
=IF(ISERROR($J2),"","X")

[TABLE="width: 609"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Email Address[/TD]
[TD]Status[/TD]
[TD]Details[/TD]
[TD]Ref[/TD]
[TD]Assigned[/TD]
[TD]Type 2[/TD]
[TD]Type 3[/TD]
[TD]Duplicate[/TD]
[TD]Cell Ref[/TD]
[TD]Type 4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]test@test.com[/TD]
[TD]Strong[/TD]
[TD]Science[/TD]
[TD]None[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[TD]No[/TD]
[TD]apple@test.com[/TD]
[TD]$C$3[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]apple@test.com[/TD]
[TD]Medium[/TD]
[TD]IT[/TD]
[TD]None[/TD]
[TD]No[/TD]
[TD="align: right"]1[/TD]
[TD]Yes[/TD]
[TD]test@test.com[/TD]
[TD]$C$2[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]sample@test.com[/TD]
[TD]Strong[/TD]
[TD]IT[/TD]
[TD]R12345[/TD]
[TD]Pending[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]test2@test.com[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Re: Enter value 2 columns to right of cell address reference

Hi Cyrus,

I posted my formula for Column J in case this needed to be modified. You will fully understand why after this post now.

This is partially correct, but it's the actual cell reference address of the string returned and not the cell reference.

Where it shows J2 returned address "$C$3" - so were focusing on reference "C3" and wanting to put an "X" in K3.

As you know J3 returned "$C$2" so we focus on "C2" and put an "X" in K2.

So you could say it's 8 columns to right of Column C or 1 column to right of Column J (Cell reference).

Finally, we have J4 and this returned "#N/A" meaning nothing happens. No matches found.

Hopefully that makes more sense?

Sorry again for the confusion

Cheers

Chris

 
Last edited:
Upvote 0
Re: Enter value 2 columns to right of cell address reference

Hi Chris,

I'm sorry, but i'm not fully understanding.

Question 1: Which column do you need your formula in?
Question 2: Why does J2 say C3, why do you want to reference a cell address that has 'Strong' in it?
 
Last edited:
Upvote 0
Re: Enter value 2 columns to right of cell address reference

Hi Cyrus,

Sorry for all of last night's confusion. I shouldn't have posted this thread whilst in a sleep deprived state.

Question 1: Which column do you need your formula in?
Click to expand...

I require a formula to run down Column K. It returns the actual location (cell address) of any duplicate email address found when checked against Column B. When this happens, I want it to return an "X" on the same row as the cell address, but in Column K.

A valid cell reference in my view is anything other than "#N/A". This was obviously poor work on my part as "#N/A" means no duplicates were found. I would have preferred to leave this blank when it found no duplicate records as "#N/A" is very messy.

Question 2: Why does J2 say C3, why do you want to reference a cell address that has 'Strong' in it?
Click to expand...

This is where it gets even crazier. I don't actually want it to return Column C in the cell address, but it was the closest formula could come up with to get to my desired outcome. All I wanted to do was compare emails on each row of Column I against the same row on Column B to find duplicates. When a duplicate is found it marks this row on Column K with an "X" otherwise it leaves it completely blank.

I take it I have used the wrong formula in Col J making things far too complicated???

Cheers,

Chris
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
In column A locate partial text from column C then apply corresponding column D text to column B
Replies
3
Views
322
hagia_sofia
hagia_sofia
A
  • Solved
referenced blank cell returned zero (0) as values
Replies
5
Views
408
Peter_SSs
Peter_SSs
ChristineJ
  • Question Question
Function rather than macro to return cell references
Replies
3
Views
333
Micron
M
ChristineJ
  • Solved
Adapting code to return cell values rather than cell references
Replies
2
Views
375
ChristineJ
ChristineJ
willow1985
  • Solved
IF AND formula, returns "Yes" if column matches a cell reference and 2nd column starts with first 3 characters in a reference list
Replies
6
Views
249
Cubist
Cubist

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top