PDA

View Full Version : Excel question


vegyjones
20th February 2007, 13:30
Right, getting off the plumbing questions in the PC section and back to computer advice :doh

If I have a list in excel as such

Files 3456-3458
Files 3459-3478
Files 3479-3484

Can I do a search for File 3465 to get it to bring up the 2nd item in the list (3459-3478)???
mathare
20th February 2007, 13:36
If I have a list in excel as such

Files 3456-3458
Files 3459-3478
Files 3479-3484

Can I do a search for File 3465 to get it to bring up the 2nd item in the list (3459-3478)???Why would that search bring up the 2nd item in the list? 3465 doesn't appear anywhere in the data :doh
vegyjones
20th February 2007, 13:37
No, but I have some stuff to jot down.
It will say something like 3459 to 3478

Is there no way I could put that down
so that it would know a file in between those numbers is there!
mathare
20th February 2007, 13:41
No, but I have some stuff to jot down.
It will say something like 3459 to 3478

Is there no way I could put that down
so that it would know a file in between those numbers is there!Oh! I see! They are numerical ranges not hyphenated numbers, right.

How are you planning to use this search facility? A normal find works on a string match which wouldn't work in this case as a search for 3465 wouldn't find a match anywhere in your data.

You need the row (basically) containing a file number lower than (or equal to) the search string and one greater than (or equal to) it don't you? What will you then do with that row number?
vegyjones
20th February 2007, 13:49
Every few set of files will be in an individual box, so the search wil only be to determine which box the particular file is in!

So if I do the start and finish file in different columns,
and then do search for file number it will take me to the closest figure, yes?
mathare
20th February 2007, 14:02
Every few set of files will be in an individual box, so the search wil only be to determine which box the particular file is in!

So if I do the start and finish file in different columns,
and then do search for file number it will take me to the closest figure, yes?Enter the start file number in one column, the end file number in another and the box number in a third (hidden) column. Then use the LOOKUP function to return the box number, as in the attached.
vegyjones
20th February 2007, 15:17
Excellent Mat!

The lokup thing is a great idea too!

:hearty