Returning a cell contents using "Find"

[Mark Flaxman]

Hi hope you can help.

I am using a spreadsheet which has 3 worksheets.
Worksheet 1 is an introductory worksheet.

Worksheet 2 has data entered by a user who has to send
special "files" to different offices. Each row in the
spreadsheet records a file ID, who it was sent to and
when. It also displays the current location of the file,
(data extracted from worksheet 3).

Worksheet 3 is used by those people who receive the files
to record when it was received, and their action, eg, the
date they return it or who they send it on to, and when.
This worksheet is formatted in the same way as WS 2, so
that if a file ID number 10001 is entered in row 22 of WS
2, being sent to "The City" Office on 25 December, WS 3
will have the same file details in row 22. All other
movements of that file are recorded in later columns in
the same row.

Perhaps this is a strange way of recording data. Firstly
a database would be better, but for various reasons the
company I work for does not use databases for simple
tracking like this. Secondly, it may have been better to
record "all" the movements on one worksheet, but WS 2 is
meant to be a control worksheet for the person who has
overall control of the files.

My problem is.....

I want to create a Find facility in worksheet 1. The user
will type the ID into a cell, and in the next couple of
cells in the same row will be displayed the Row Number
where he entered the file in Worksheet 2, and also an
instant quick reference to the "current location" of that
file. The row number will give the user an instant
reference of which row number to look at in Worksheet 3
for further information.

How can I do capture which row the ID was entered in?

I have tried HLOOKUP and other reference functions, but
they only seem to reference the same rows as the row the
cell function is in.

In Worksheet 1 the input cell is fixed in one position,
but the data it targets could be in any row in Worksheet
2 or 3, so seemingly HLOOKUP doesn't work.

Sorry for the length of this. I am not a newbie to Excel,
but I am no expert either, and this has me stumped. I
have been searching this forum but at page 9 my will to
live is wilting,

 

[Max]

Assuming this is the kind of set-up
you have in Sheets 2 & 3
(inferred from your post):

In Sheet2 (cols A to F, data in row2 down)
--------------------------------------
File ID..Received on..Action..Sent to..When.....Curr Location
10001..20-Dec-03....Filing....John..25-Dec-03..The City Office
10102
10233
etc

In Sheet3 (cols A to F, data in row2 down)
----------------------------------------
File ID..Received on..Action....Sent to..When......Curr Location
10001....25-Dec-03....xyz done..Mark...25-Dec-03..The Store

Try along these lines in Sheet1
------------------------------------
File ID..Row#..Curr Location
10001...2.......The Store

Put labels in row1
A2 is for the input of File ID,
B2 & C2 contain formula reading A2's input

Put in:
B2: =MATCH(A2,Sheet2!A:A,0)
C2: =OFFSET(Sheet3!$A$1,MATCH(A2,Sheet3!A:A,0)-1,5)

When user keys in File ID in A2,
B2 returns the row# from Sheet2
C2 returns the Current Location from Sheet3

-------------------------------------

If you want error handling (eg: alert messages, dashes)
in the event of no matches found for the ID in A2,
use an IF(ISNA(function),<alert>,(function)) construct

Instead of the above formulas in B2 & C2, try:

in B2:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"No such ID",MATCH(A2,Sheet2!A:A,0))

in C2:
=IF(ISNA(OFFSET(Sheet3!$A$1,MATCH(A2,Sheet3!A:A,0)-1,5))," ---
",OFFSET(Sheet3!$A$1,MATCH(A2,Sheet3!A:A,0)-1,5))

Hope the above eases you into some ideas you can use.

Rgds,
Max

 

[Guest]

Ahh Max,

You are a "STAR"!!

You got the idea in one, and your solution works like a
dream.

In Sheet 2 I gave the File ID range of cells a Name. I
was quite innovative, I used the name File_ID, <grin>.

I used that name in the Match formula, instead of
Sheet2!A:A, as visually it made more sense to me. (I had
to include at the beginning of the formula, "3+(Match,
etc)" to return the correct row # as rows 1 to 3 in the
Sheet 2 were header rows, but it worked perfectly).

I'm still working out how the Match function does it, and
I'm still playing with the Offset function as it isn't
working for me yet, but that is probably because I
haven't entered it correctly.

BUT YOU ARE A STAR!!! You have solved my problem.

If you were the opposite sex I would kiss you!!

Many thanks m8

Mark Flaxman

 

[Mark Flaxman]

Oops, sorry, in my excitement I forgot to enter my name
in my reply.

 

[Max]

Mark, you're welcome !
Glad it helped ease you in.
Thanks for the feedback.

cheers
Max

 

[BrianB]

I think you need =VLOOKUP()