Im looking for a macro or formula to look for a value in a cell from a range in another sheet, then return a value 1 if found or 0 if not found.Then r

L

lgperformanceanalyst

New Member
Joined
Feb 15, 2013
Messages
8
I have raw data on a sheet called 'Data' imported from a CSV
On the sheet 'Analysis' I would like to look for a value in column F, For example 'F3' which is "1.1.1Yes" Note :this is reference from another sheet within workbook (='20141031_CurriculumAthletics'!A3').

Therefore look for the text in F3 within a specific range 'Data!D2:FV129' in another sheet. If the value of F3 is within range return a value of 1, if it is not within range 0, return either value in cell J3 in sheet 'Analysis'.

I would then like to repeat for any values in column F and return the 0 or 1 value to column J.

Any assistance would be greatly appreciated.:biggrin:

Please excuse if I have used the wrong terminology.

Thankyou
 
Sort by date Sort by votes
Thankyou for your response. The value in F3 should not appear more than once but the value could appear anywhere within the range. Any help would be greatly appreciated.
 
Upvote 0
How about a UDF..

Paste this into a standard code module and use as below..
Code: 
Function InRange(f, Lookin As Range)
Dim r As Range
Set r = Lookin.Find(f, LookAt:=xlWhole, Lookin:=xlValues)
    InRange = (Not r Is Nothing) * -1
End Function

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
FGHIJ
31.1.1Yesa1
41.1.1Yesb0
51.1.1Yesc0
61.1.1Yesd1
71.1.1Yese0
81.1.1Yesf0
91.1.1Yesg0
101.1.1Yesh0
111.1.1Yesi0
121.1.1Yesj1
131.1.1Yesk0
141.1.1Yesl0
151.1.1Yesm1
161.1.1Yesn0
171.1.1Yeso1
Sheet2
Excel Workbook
DEFGH
41.1.1Yeso
5
6
7
81.1.1Yesj
91.1.1Yesd
10
11
12
131.1.1Yesa
14
15
16
171.1.1Yesm
Data


</body></html>
 
Upvote 0
lgperformanceanalyst said:
How do I mark this a solved in the forum? Thankyou
Click to expand...

Hi, glad it worked for you - threads are not marked as solved here, this approach encourages others to post alternative suggestions (which may well be better suited than the one that happened to solve the problem first).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Lookup a string of text in one sheet and return adjacent value from another sheet
Replies
1
Views
585
arun_eagertolearn
A
N
  • Question Question
VBA to look for reference and then copy and insert value only into another sheet
Replies
1
Views
299
Nova1979
N
A
  • Solved
referenced blank cell returned zero (0) as values
Replies
5
Views
762
Peter_SSs
Peter_SSs
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
641
MARZIOTULLIO
M
E
  • Solved
Find lowest LEN difference & return contents of cell to left
Replies
5
Views
190
Sufiyan97
Sufiyan97

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