[users] Re: Calc & Database

[Curtis Vaughan]

My last post was formatted wrong, so I'm reposting. Sorry for reposting.

Begin forwarded message:

From: Curtis Vaughan <[EMAIL PROTECTED]>
Date: 15 июня 2006 г. 12:56:46 GMT-07:00
To: Claudia Drechsle <[EMAIL PROTECTED]>
Subject: Re: [users]  Re: Calc & Database


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

Claudia,

Hope you don't mind me writing to you directly, but I don't really want to have to post all this information on a public forum.

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