I work for a document storage company, we use Excel to search for locations of our boxes.
One of our companies numbers each file individually, ie, file 1, file 2, file 3. This means that they fit up to ten to fifteen files in a box.
The way that they number there boxes for example is 1-10, if there where ten files in that box.
Lets say the company asks for file 8 to be retrieved. how do i search specifically for that file number without going through the whole box list til i come across "1-10" where it is ?
2006-09-28
22:48:55
·
8 answers
·
asked by
PEO
1
in
Computers & Internet
➔ Software
Here is an easy way:
In column A, put a list of file numbers, but only the first file in the box. In column B, you can have the full label of the box, such as '1-10. (notice the ' to make it text.) In column B you put your location in your facility.
Now in cell E1, you put an example file number you are looking for. This cell will be where you put the exact file you seek. Other cells will tell you the box label and the location code.
In cell F1, you put a formula. My example has only five rows, you would select all the rows of your list. =VLOOKUP (D1,A2:C6,2)
This does a vertical look-up of what is in D1, in the range in columns A to C and it returns what is in col 2 (or the B Col) This returns the box label.
In cell F2 or G1, you can put another formula. =VLOOKUP (D1,A2:C6,3). This one returns the info in col 3, the location you entered.
VLookup requires that the first column be sorted in ascending order. It searches for the next largest value that is less than what you are looking for, unless you tell it to find an exact match.
2006-09-30 04:26:06
·
answer #1
·
answered by Ken C. 6
·
0⤊
0⤋
A very tiresome and long winded way of doing it would be to have a different page/tab for each box and then to list the files contained within it on that page. Not a very good way if you have lots of files.
You could always do a search for the files using Excel's search/find feature, which can be one on an individual page OR the whole workbook.
If it's only the one company doing this, then my first suggestion would be ok. If it applies to all of them (which I don't think it does reading what you say) then that should solve the problem. Or at least I hope it does, if you can understand me. :)
2006-09-28 22:56:15
·
answer #2
·
answered by mancunian_nick 4
·
0⤊
0⤋
If the file number is held in a separate column, you could put a filter on that column (Data ... Filter ... Autofilter), then on the heading of that column you'll see a drop-down list box of all values in that column. You can choose the value you want and it will show only the line with all the details, including the box.
Afterwards, take off the filter by repeating the steps above or choose (All).
2006-09-28 22:53:55
·
answer #3
·
answered by gvih2g2 5
·
0⤊
0⤋
Lemme guess, you're in a working laptop or computing gadget Literacy class... according to probability with the artwork Institute??? because of the fact whats humorous is it fairly is the best question of this weeks first project. bizarre. "Week 3: Week 3 - W3: project one million communicate communicate question 3 learn Microsoft be conscious, Microsoft Excel, and Microsoft PowerPoint. whilst could you utilize each of those purposes? whilst are you able to apply those purposes interchangeably? provide examples and motives to assist your answer."
2016-10-01 12:01:31
·
answer #4
·
answered by ? 4
·
0⤊
0⤋
Are these files, worksheets and boxes workbooks or everything is in one spreadsheet, or are they rows and columns? Or haven't you created the spreadsheet (list/database) yet and you want suggestions? This wouldn't be very difficult to solve, you need to be a bit more specific and then I'll edit my answer.
2006-09-28 23:38:19
·
answer #5
·
answered by Yellow Dice 2
·
0⤊
0⤋
One thing you should consider is that you are doing a database type operation on Excel which is a spreadsheet piece of software.
You could create a macro, or funtion within the document.
2006-09-28 23:18:17
·
answer #6
·
answered by budda m 5
·
0⤊
0⤋
Couple of options
Eiter use the Data - Autofilter
Or just do a search Edit - Find
2006-09-28 22:55:05
·
answer #7
·
answered by Ross H 2
·
0⤊
0⤋
hold down ctrl and F together this is the find option, then just type in what your looking for
2006-09-28 23:05:15
·
answer #8
·
answered by clutterbug84 3
·
0⤊
0⤋