* Eike Rathke <[EMAIL PROTECTED]> [090305, 15:17]:
> Hi Ennio-Sr,
>
> On Tue, Mar 08, 2005 at 01:38:06 +0100, Ennio-Sr wrote:
>
> > 1st problem:
> > I copied a list of shares in Sheet1 of a new spreadsheet (ss) and
> > established a link to an url (which gives the latest quotation) in Sheet
> > 2 of the same ss.
> > All works very nicely but ...
> > If I assign named ranges to each of the cells where the quotations are
> > and then add a new share (a new row) in the original URL table, when
> > sheet2 refreshes the link the new row disrupts the old ranges.
> > May be I found a way to avoid this: first insert a new blank row in
> > sheet 2 and then refresh the link, but perhaps there is a better way...
>
> I don't quite understand what you mean by "the new row disrupts the old
> ranges". Could you please give an example of what happens exactly, and
> the names you defined and the formulas you use?
>
Hi Eike,
I'll try to make an example:
Original web table, stage 1:
______________________________________________________________
| | A | B |..........
--------------------------------------------------------------
1|Item a | | 10.15 |
2|item c | | 22.30 |
3|item d | | 3.12 |
OOo.1.1.4 table resulting from Insert > External Data: stage 1
______________________________________________________________
| | A | B |..........
--------------------------------------------------------------
1|Item a | | 10.15 | --> defined as rn_1
2|item c | | 22.30 | --> " rn_2
3|item d | | 3.12 | --> " rn_3
Now, if I add a new item in the original web-table (and do not make
space for this new row in the OOo table), that's what happens:
Original web table, stage 2:
______________________________________________________________
| | A | B |..........
--------------------------------------------------------------
1|Item a | | 10.15 |
1a|item b | | 33.33 |--> new row in web table
2|item c | | 22.30 |
3|item d | | 3.12 |
OOo.1.1.4 table resulting from Insert > External Data: stage 2
______________________________________________________________
| | A | B |..........
--------------------------------------------------------------
1|Item a | | 10.15 |--> appears as being rn_1
1a|item b | | 33.33 |--> " " rn_2
2|item c | | 22.30 |--> " " rn_3
3|item d | | 3.12 |--> former rn_3, now shown as B4
As you can see, the new row 1a takes the place formerly assigned to row
2 and, of course, B2 priorly defined as rn_2, doesn't correspond to the
new value.
As I said it can be corrected inserting a blank row in the place that
will be occupied by 'item b' before updating the link.[IIRC the same
behaviour applied to Lotus Millennium].
> > 2nd problem:
> > When the link in sheet2 is refreshed, the new table will be copied on
> > the old one in such a way that this remains as a one-column table
> > underneath the freshly copied one:
>
> ? Please give an example.
>
This would be more difficult to represent; however, I found out why it
was happening: it depends on the position of the cursor when you update
the link. That's to say, that if you navigate your OOo imported table
and do not position the cursor to (0,0) or cell A1 before exiting the
same, when you update the link the imported table will be copied so that
the cursor position represents the upper left corner of the table: so,
if you left the cursor on cell B1, the new table will start in B1
whereas column A will still have the whole contents of the old table.
> > deletion of that column causes, of
> > course, the left shifting of all range names! [So, one reason more to
> > find a way to refer to a cell in another sheet (on the same ss) by range
> > name instead of by cell ref (ColumnRow)] (*)
>
> If you don't want references of named ranges be adapted according to
> deletions and insertions, use relative addressing instead of absolute.
> For example, define a named range as $Sheet2.A1:B4 instead of
> $Sheet2.$A$1:$B$4
It would be great if one could use range names, instead; I mean not only
in the same sheet but in another doc or sheet as well. [That is possible
in Lotus Millennium].
>
> > Before doing the link-refresh I saved-closed the ss and re-opened it
> > answering 'yes, do refresh the link'. Didn't find a way to do that
> > without re-opening the file.
>
> Menu Edit.Links, Update.
>
Oh, fine, thank you!
> Eike
>
Thanks for your attention.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (�|�)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
