Ask Your Question

Use formula result as Link to External Data

asked 2018-12-08 22:18:58 +0100

VoidNWarranties gravatar image


I think I might just be missing the simple answer but...

I'd like to import player data from a website. But I'd like to easily change what data is imported based on a calculation inside my LO Calc file.

Using concatenate I can take these inputs: Week 13 Position 1 Offset 0

and create this url link:

If you go to the website you will see this a real link with 25 rows of real player data. If you change the week or position number the web page reloads appropriately. For example: Week 13 Position 2 Offset 26

Inside calc though I have to actually update and redo each of my external links each week.

Is there a simple way to make a macro or something to save some time and effort for these steps?

Thanks so much, TC

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-12-09 06:05:32 +0100

Ratslinger gravatar image

updated 2018-12-09 06:27:00 +0100


Have attached a working sample based upon you question link.

Sample - LinkToExternalFromCellData.ods

When you open the sample you will get a message stating the link is disabled. Enable with the button provided.

Cell A1 is the Rank & A2 is the week. Enter what you want & press the Retrieve Data button.

When creating/modifying external links care is needed. You create once & modify after. The links are stored by index for the document so you need to know the index of the link to be modified. In the macro, the insert statement for the link is commented out and the link thereafter just needs the URL changed. In this sample there is only the one link.

Comments are provided in the macro for clarity.

edit flag offensive delete link more


Okay - I don't want you to think that I didn't read your answer. I did and I got it working (macro security settings can be a bear)

Anyway - THANK YOU SO MUCH. But I have to read between the lines here to make this do the entire job here.

What I really do is import the top 125 players for each position. In my neanderthal method - I have link set up with the offsets of 1, 26, 52, 78... etc and then I "index" the position and start all over. I tested and it is to change the position.

I have to think about this more but I wanted to get my "Thank You" back out to you!

VoidNWarranties gravatar imageVoidNWarranties ( 2018-12-12 05:50:23 +0100 )edit

You really should have stated your actual goal in the question. For what you want, it is little more than placing a loop in the code (For x = 1 to 5) and using an increment in the 'Rank' ('Rank * x), and finally an increment for the location to store the data. Just some minor changes.

Ratslinger gravatar imageRatslinger ( 2018-12-12 06:31:15 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-08 22:18:58 +0100

Seen: 25 times

Last updated: Dec 09 '18