On 14.06.2006, at 0:16, Claudia Drechsle wrote:
First thing I want to be able to do is have a spreadsheet or an OOo database (which I have already created) with the telephone numbers and the corresponding vessel name. Then I can query that table so that in possibly a new spreadsheet it substitutes the phone number with the vessel's name. Hi Curtis First I've to say: that's database-stuff and should be done with database-function. But in this theme I can't help. So I 'll describe how I managed a simular situation only with calc-sheets: I get every month a logfile (CSV) to analyize. So I created a Calc-spreadsheet und linked the CSV-file as an own table of the spreadsheet that can always be actalized when opend (insert/sheet:From file + Link). The CSV-File (must not nesessarily be a CSV-file) gets every month overwritten, so that the spreadsheet allways links to the newest version. In a second sheet of the spreadsheet you could place a table with the phone-number and the vessel's name (and more information, if you like). The phone-number that will be used as a key has to stand in the first column. It would be helpfull to define this table as a range: Mark the whole sheet (so you do not need to change anything when you add new lines or columns), then: Menu:Data/define range: telephone In a third sheet you merge this information, for example: In column A you write: =Sheet1.A1, where Sheet1 is the linked monthly file and A1 the telephone-number. In column B you write: =VLOOKUP(A1;telephone;2;0) that gives you the vessel's name, when the name stands in row no. 2 of "telephone". For the rest of the columns, you take the ones you need. I suppose, the number of lines may be different from month to month. So create enough copies of the line you created in sheet no. 3. You might integrate an IF-formula so that the lines keep empty, when there are no more data-lines in the linked table. For to work with datapilot I'd mark the whole third sheet, it should be possible to filter the data-pilot-results so that empty lines where unaccounted. cheers, Claudia Can't seem to get it to work right. Here's what I did. Imported data is in Sheet 1. Telephone table is in Sheet 2. I defined Sheet 2 as a Data range and called it 'telephone' (like you). So for example I have in Sheet 2. XProvider A B C D N Vessel Reference Tel Number 2 ZH Zhanna Sh 881621427381 3 OS Ostrov Iony 881621427382 4 AD Arctic Discovery 881621427383 5 AO Arctic Orion 881621462558 6 PO Pacific Orion 881621427385 7 BH Vostok orion 881621427386 8 VK Vostok1 881621427398 9 SH Shursha 881621427387 10 TR Tarutino 881621427388 11 VR Viera 881621427389 12 SF Serena 881621427390 13 BL Berill 14 BR Brig 881621427392 15 VA Vostok Adonis 881621427393 16 VS Vostok Sirius 881621427395 17 VV Vostok Vega 881621427391 18 FKV FKV 881621427396 19 NDO NordOst 881621427397 20 NPC NPC 881621465295 21 PUS PUSAN 881621462478 So then I made a DataPilot table in Sheet 3 based on information in Sheet 1. Originating ID 881621427381 703.02 881621427382 1,546.82 881621427383 1,350.24 881621427385 1,446.82 881621427386 1,283.13 881621427387 1,136.46 881621427388 933.84 881621427389 91.35 881621427390 76.21 881621427391 4.50 881621427393 762.75 881621427395 526.12 881621427396 732.14 881621427398 963.47 881621462558 1,217.42 881621465295 5.38 Total Result 12,779.67 So I want to cross reference the telephone numbers in the Data Pilot table to the information in Sheet 2. So, I did this: =VLOOKUP(A4;telephone;3) A4 = the first Originating ID telephone number in the DataPilot table. However regardless of which cell I point to in the DataPilot table (A3 - A19) the result is always the same: PUSAN. If I change the final value 3 to 2, it does change to PUS, etc. I thought maybe VLOOKUP isn't the right function, but HLOOKUP doesn't seem to want to work at all. I appreciate your input! Curtis
