Roger,
I shall top post as I note this is your preference.
My speaker list is already 60 people long and more will be added. Having
speakers appended means that without sorting I have to eye scan
alphabetically to see if they are in that part of the column then eye scan
the unsorted part of the column which is inconvenient and also not practical
if at any time I have to let others work on the file.
I'm getting confused as I thought that Excel kept cells relative when moved
(i.e. sorting names in the names sheet) except when $ was added in front or
or or column or both address?
Beemer
| Hi
|
| I think what you need to do is add an extra column (E) to the Speaker
| sheet with Date as the heading.
| Just enter the proposed date of the speaking engagement in there.
|
| On your Syllabus sheet in cell B2 enter
| =INDEX(Speakers!$C$1:$C$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))
| in C2 enter
| =INDEX(Speakers!$D$1:$D$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))
|
| You shouldn't need to do any sorting of the Speaker sheet, just amend
| the dates on Speaker sheet, and the relevant date will appear in your
| syllabus.
|
|
| --
| Regards
|
| Roger Govier
|
|
| | >
| > | > | 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
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
|
|
|
