| Hi
|
| Take a look at Help on Vlookup.
|
| The generalised form
| =VLOOKUP(lookup_Value,table_range,offset,[True or False])
| Basically, if you have a table of 2 columns of Data, A and B,
Vlookup
| will search column A of the table, and return the value that is
offset a
| given number of columns to the right.
|
| In the example I posted, I just gave a supposed range of A1:B100
where
| the table existed. The 2 represented taking the value from column B,
the
| second column in the table.
| The 0 is the same as writing FALSE, as the 4 th argument tot he
formula,
| which ensures it will only look for an exact match, not an
approximate
| one.
|
| I could equally (if I knew your data layout have said
| =VLOOKUP("Roger",A:G,5,0)
| which would have looked for the name Roger in column A, and, if
found
| return the value from the same row that resides in column E
|
| --
| Regards
|
| Roger Govier
|
|
| | >
| > | > | No, this is not a bug.
| > | If you have cells on Sheet1 pointing to other cells on Sheet2
with a
| > | direct reference like =Sheet2!B1, then if you change the
contents of
| > | what is in those cells on Sheet2, by sorting sheet2, then Sheet1
| > will
| > | return (quite correctly) what is now in those cells.
| > |
| > | If, on the other hand, you were using a Vlookup formula, then it
| > would
| > | be possible to return the same value even after sorting Sheet2
e.g.
| > |
| > | =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
| > |
| > | --
| > | Regards
| > |
| > | Roger Govier
| > |
| > |
| > | | > | > If an Excel 2007 sheet (#1) has formula referenced cells to
| > another
| > | > sheet
| > | > (#2), and if the column in sheet (#2) containing the
referenced
| > cells
| > | > is
| > | > then A-Z sorted on the value then the original references in
sheet
| > #1
| > | > get
| > | > lost as they are now pointing to different cells.
| > | >
| > | > Is this an Excel 2007 bug?
| > | >
| > | > Why after the sort does the relative position not move so
keeping
| > the
| > | > correct references?. I have not used $ in the cell address.
| > | >
| > | > Beemer
| > | >
| > | >
| > | >
| > Roger,
| >
| > I am not familiar with VLOOKUP but I tried to implement it using
your
| > formula. I'm getting "value not available error". I do not
| > understand the
| > last part of the formula :$B$100,2,0 Where does the "B100" come
from
| > and
| > the "2" and "0"
| >
| > Beemer
| >
| >
| >
| >
Roger,
I don't think my requirement will fit with VLOOKUP. Here is a
different
description of what I am trying to do:
I have a club syllabus to develop. I collect the names and details of
as
many speakers I can find. From this list I select who I want or who
is
available on a Thursday of the months Sept to May. People change
their
minds about the date or drop out so I need to be able to link to
others in
the speaker list.
The layout is that one sheet contains a column of speaker names and
its rows
hold their details including topic. The other sheet is the actual
syllabus
which will eventually be published. So the syllabus sheet is a column
of
monthly Thursdays Sept to May.
The four columns of the speaker sheet are A First Name B Second Name
C
Concatenated D Topic
e.g.
A B C D
1 First Second Name Topic
2 Mickey Mouse Mickey Mouse Cats
|
|
|
50
The above keeps geeting new names added.
The Syllabus sheet contains:
A B C
1 Date Name Topic
2 06/-9/2007 Mickey Mouse Cats
|
|
|
37 15/05/2008
I start in the Syllabus sheet and enter the reference formula in B2
linking
to C2 in the Names sheet. All is well until I insert/delete a name
or
append to the end and then do a sort. I am just a beginner with
Excel and
Access but I chose Excel to do the task as I did not know how to work
with
dates in Access.
Can you see any light in me acheiving a positive solution?
Beemer
