Is there such a formula/rule?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ReneeDee - 09 Jun 2004 02:47 GMT

I have a spreadsheet with over 2,000 records (only two columns are used).
Column one has a unique number. Column two has a range of numbers. For
example, Column One might have the number 12345; and Column Two would have
typed in it: 2004685-2004921. What this means is that in Box #12345, we
have packed in that box File Numbers 2004685, 2004686, 2004687 all the way
to File Number 2004921. After typing all these records, I was then told we
need to have some kind of formula or rule whereby we can locate a particular
file number in between that range so that we can find what box number it's
in. In other words, I might need to find what box file number 2004863 is
located in.

Rather than retyping the entire spreadsheet with each number from the range
individually, is there a formula or a search rule that can find a number
within a range?

If not, can it be done in Access?

Any help or suggestions would be greatly appreciated.

Reply to this Message

Ron Rosenfeld - 09 Jun 2004 03:41 GMT

>I have a spreadsheet with over 2,000 records (only two columns are used).
>Column one has a unique number. Column two has a range of numbers. For
[quoted text clipped - 14 lines]
>
>Any help or suggestions would be greatly appreciated.

It would be useful if you would tell us what the problem is with the previous
answers you received to this question. You first posted it several weeks ago,
in more than one newsgroup, and received multiple suggestions.

So please review those responses and let us know what the problems were with
those suggestions, so we don't waste our time.

--ron

Reply to this Message

ReneeDee - 09 Jun 2004 04:56 GMT

This is my first posting. I never posted here before. I also posted
tonight for the first time at comp.apps.spreadsheet. Last week I did post
on the Microsoft web site, but got no responses. That was my very first
posting.

> >I have a spreadsheet with over 2,000 records (only two columns are used).
> >Column one has a unique number. Column two has a range of numbers. For
[quoted text clipped - 23 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 09 Jun 2004 10:22 GMT

>This is my first posting. I never posted here before. I also posted
>tonight for the first time at comp.apps.spreadsheet. Last week I did post
>on the Microsoft web site, but got no responses. That was my very first
>posting.

Must be another Renee with the same problem, that posted messages May 25-26
(two weeks ago), on the microsoft.public.misc; .programming; and
.worksheet.functions news groups, AND received answers in each of those news
groups.

Do a Google search for someone with the same first name as you, in the excel
newsgroups during the past month, and see if the solutions match.

Here is what the other Renee posted:

===========================================
From: Renee (info@yourofficeassistant.net)
Subject: Is there a formula for this?

Newsgroups: microsoft.public.excel.programming
Date: 2004-05-26 09:34:29 PST

I have an Excel spreadsheet with 2 columns. The first
column has a Box number (example: 01245). The second
column has the file numbers that are in that particular
box (example: 821-873). So, in other words, box #01245
contains files 821, 822, 823, 824, etc. all the way to
873.

Obviously it's difficult to find a particular file number
if we don't know what file number range it's in. Rather
than list each file number separately (Example: first
column I would list the box number and then in the 2nd
column list the first file #, then I would go down to the
next row and list the box # again with the next file #
and so forth) in order to search for a specific file #.

Is there a formula of some kind whereby I can continue to
include the file # ranges (example: 821-873) but be able
to do a search to find one specific file number
(example: I want to find file # 846)? Is this possible
and if so, how do I do it?

Thanks for any help anyone can give me!
==============================================

There were multiple solutions given in each of the news groups that Renee
posted in before.

--ron

Reply to this Message

Jerry W. Lewis - 09 Jun 2004 12:33 GMT

The issue is not location, but multiplicity of posts. People have
independently answered this question in 4 other locations

http://groups.google.com/groups?threadm=12bf001c4433e%24f9e515f0%24a401280a%40phx.gbl
http://groups.google.com/groups?threadm=1304001c4433f%2411501c80%24a101280a%40phx.gbl
http://groups.google.com/groups?threadm=1278701c442bb%24596fc030%24a101280a%40phx.gbl
http://groups.google.com/groups?threadm=YJOdnchNoPN--VvdRVn-sw%40comcast.com

If you are convinced that a question belongs in multiple newsgroups,
then address one post to all of the relevant newsgroups, so that
responders will be aware of all the responses and not waste their time
on previously answered questions.

HTH
Jerry

> This is my first posting. I never posted here before. I also posted
> tonight for the first time at comp.apps.spreadsheet. Last week I did post
[quoted text clipped - 59 lines]
>>
>>--ron

Reply to this Message