Excellent! Many thanks, Brian. I'm sure it will work ;-)
On Jun 29, 2008, at 11:48 PM, Brian Barker wrote:
At 21:26 29/06/2008 +0300, Alex Zachopoulos wrote:
I've got this Calc file with a sheet showing:
First Name, Last Name, Cell Phone
and another sheet showing:
LectureDate, SpeakerLastName, SpeakerFirstName, SpeakerCellPhone,
LectureTheme
I need to do this:
In the 1st sheet, I've already entered speakers' names and phones.
In the 2nd sheet, I am entering the lectures' data: date, speaker
name, and theme. But I need the cell phone number to be entered
automatically from the 1st sheet. Then I need to export to a .csv
to import to FileMakerPro and from there export an iCal file to
import to my iCal and from there to my cell phone.
If you're still following this, the part that I don't know how to
do is the auto-entering of the cell phone. Some sort of Lookup? But
what exactly?
Your first problem here is that you need to match two bits of data -
both parts of the name - rather than just one. The easiest way is
probably to concatenate these first. So I'm going to suggest that
you create a new column C (called "WholeName"?) in your first sheet
("Speakers"?). In C2 enter the formula =A2&B2 and copy this down
the column. You will now have values such as "ZachopoulosAlex" in
column C. Similarly, create a new column D ("SpeakerWholeName"?) in
the second sheet, enter =B2&C2 in D2 and fill down the column.
Now the lookup is fairly easy. In E2 of the second (lectures)
sheet, enter the formula
=VLOOKUP(D2;Speakers.C$2:D$99;2;0)
and copy this down the column. D2 is here the whole name of the
speaker you are looking up. Speakers.C$2:D$99 is the array of whole
names and phone numbers (on the other - "Speakers" - sheet). The
"2" indicates that it is the second column - D - of this array which
contains your desired result, and the "0" (or "FALSE") indicates
that the whole name values are not sorted.
If you wish, you can hide the two new columns once you have set
everything up, of course.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
