Hello~

Im trying to create a formula based on ranges of numbers, as well as single
value numbers in a spreadsheet to where the out put is a value of either Y
(for yes) or N (for no) depending on where in the range of numbers my search
criteria falls........I was thinking an IF THEN OR statement, but cannot seem
to figure out the 'nesting' part of it correctly........ Here is an example
below:

I am looking for the below numbers, or RANGE of numbers on my spreadsheet,
and if found I need the output value to be Y......If NOT found, the value
returned needs to be N.
00000;00999
06380
06460
06510
06440
02430;02440
06400;06410
06520
06540

Not sure what to do.

Thanks-

Mary

the following function MAY do what you are asking. I returns a "Y" if it
finds one of the values in the range of cell you specify. Otherwise it
returns "N". Not suurre if it works like you want for a range containing
multiple cells. Did you want it to return "Y" if all the cells were in the
range, or if it found at least one cell in the range. I can easily modify
the function to meett your requirements.

What you want is very hard to do in an Excel spreadsheet.


Call with
=MySpecialRange(A5)
or
=MySpecialRange(A1:H100)

Function MySpecialRange(Target As Range)

For Each cell In Target

If Target = 0 And Target <= 999 Then
MySpecialRange = "Y"
Exit Function
End If
If 2430 = 0 And Target <= 2440 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6400 And Target <= 6410 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6380 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6460 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6510 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6440 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6520 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6540 Then
MySpecialRange = "Y"
Exit Function
End If
Next cell
MySpecialRange = "N"
End Function


"Mary C" wrote:

Hello~

Im trying to create a formula based on ranges of numbers, as well as single
value numbers in a spreadsheet to where the out put is a value of either Y
(for yes) or N (for no) depending on where in the range of numbers my search
criteria falls........I was thinking an IF THEN OR statement, but cannot seem
to figure out the 'nesting' part of it correctly........ Here is an example
below:

I am looking for the below numbers, or RANGE of numbers on my spreadsheet,
and if found I need the output value to be Y......If NOT found, the value
returned needs to be N.
00000;00999
06380
06460
06510
06440
02430;02440
06400;06410
06520
06540

Not sure what to do.

Thanks-

Mary

Hi Joel~
I have pasted a piece of the actual work sheet in this reply......The last 4
columns are the columns I am needing to either populate a 'Y' or 'N'
in.......Say if the number in column E is in the range / or list of numbers I
am searching for, then ALL
FOUR of the last columns needing either a 'Y' or a 'N' will be a
'Y'........If the number in Column E (or F for that matter) is NOT with in
the range / list of numbers I am searching for then the result to populate in
the last 4 columns will be a 'N'.........

So basically I am wanting to search ALL the numbers in columns E & F on the
entire spreadsheet.......Some WILL be with in my range / list of numbers I'm
looking for, hence, a Y and some WILL NOT be what I am looking for, resulting
in a N........
I didn't know if I could somehow do a 'Conditional Formula' or 'IF, AND, OR,
statement' ( or any other formula to help me!) I was even thinking maybe a
'Macro'??????? I dont know...........

I so do appreciate your wanting to help me, as I am at a road block, and up
until this point, felt VERY confident / advanced where as my excel skills
were concerned ;-) Now I just feel like a big DUM DUM! We can continue to
communicate thru this, or here is my work email ( Might be easier / faster
etc....0


I also noticed of course, none of the formatting from my spreadsheet carried
over in my 'copy and paste' below......Might be helpful if I could actually
eml you a portion of the attachment?!?

Take Care-

Mary


S T User ID Object Name Data Item From Data Value Thru Data Value Add
Y/N Chg Y/N Dlt Y/N View Y/N
4 URS000800 F0006 CO 00000 00999 Y Y Y Y
4 URS000800 F0006 CO 01000 06379 N N N N
4 URS000800 F0006 CO 06380 06380 Y Y Y Y
4 URS000800 F0006 CO 06381 06459 N N N N
4 URS000800 F0006 CO 06460 06460 Y Y Y Y
4 URS000800 F0006 CO 06461 06509 N N N N
4 URS000800 F0006 CO 06510 06510 Y Y Y Y
4 URS000800 F0006 CO 06511 99999 N N N N
4 URS000800 F03B11 CO 00000 00999 Y Y Y Y
4 URS000800 F03B11 CO 01000 06379 N N N N
4 URS000800 F03B11 CO 06380 06380 Y Y Y Y
4 URS000800 F03B11 CO 06381 06419 N N N N
4 URS000800 F03B11 CO 06420 06420 Y Y Y Y
4 URS000800 F03B11 CO 06421 06459 N N N N
4 URS000800 F03B11 CO 06460 06460 Y Y Y Y

"Joel" wrote:

the following function MAY do what you are asking. I returns a "Y" if it
finds one of the values in the range of cell you specify. Otherwise it
returns "N". Not suurre if it works like you want for a range containing
multiple cells. Did you want it to return "Y" if all the cells were in the
range, or if it found at least one cell in the range. I can easily modify
the function to meett your requirements.

What you want is very hard to do in an Excel spreadsheet.


Call with
=MySpecialRange(A5)
or
=MySpecialRange(A1:H100)

Function MySpecialRange(Target As Range)

For Each cell In Target

If Target = 0 And Target <= 999 Then
MySpecialRange = "Y"
Exit Function
End If
If 2430 = 0 And Target <= 2440 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6400 And Target <= 6410 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6380 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6460 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6510 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6440 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6520 Then
MySpecialRange = "Y"
Exit Function
End If
If Target = 6540 Then
MySpecialRange = "Y"
Exit Function
End If
Next cell
MySpecialRange = "N"
End Function


"Mary C" wrote:

Hello~

Im trying to create a formula based on ranges of numbers, as well as single
value numbers in a spreadsheet to where the out put is a value of either Y
(for yes) or N (for no) depending on where in the range of numbers my search
criteria falls........I was thinking an IF THEN OR statement, but cannot seem
to figure out the 'nesting' part of it correctly........ Here is an example
below:

I am looking for the below numbers, or RANGE of numbers on my spreadsheet,
and if found I need the output value to be Y......If NOT found, the value
returned needs to be N.
00000;00999
06380
06460
06510
06440
02430;02440
06400;06410
06520
06540

Not sure what to do.

Thanks-

Mary