Cell values

Post Reply
User avatar
Léon
Posts: 58
Joined: Thu Dec 17, 2020 10:22 pm
Location: Netherlands

Cell values

Post by Léon »

Is it possible to enter data from another document in a formula in which you add cell values from various worksheets?
m3city
Posts: 7
Joined: Wed Aug 17, 2022 10:44 am

Re: Cell values

Post by m3city »

How about this setup below, maybe it will give you a hint for your case.
I have two spreadsheets, named "first file" and "second file", both saved to same location.

column A in first file has static values of 10, 20, 30
ksnip_20220817-213739.png
ksnip_20220817-213739.png (8.93 KiB) Viewed 4781 times
column C in second file has static values of 1, 2, 3
ksnip_20220817-213305.png
ksnip_20220817-213305.png (2.62 KiB) Viewed 4781 times
Then I copy that column C3:C5 and Paste Special in the first file in cell B4, but select "Paste reference"
ksnip_20220817-213522.png
Note that contents of the cell D4 now has reference to that second file:
='[second file.pmdx]Sheet1'!C3

If you use ribbon interface, then you can go to Data - External references to manage that connection between files.
User avatar
Léon
Posts: 58
Joined: Thu Dec 17, 2020 10:22 pm
Location: Netherlands

Re: Cell values

Post by Léon »

Hello m3city,

The procedure you describe is new to me and certainly interesting.

But I would rather not paste the data from the second file into the first file.

Suddenly I made a remarkable discovery: I already have a document for a long time which contains a reference which is exactly what I am looking for.

Please see the formula in the image.

Image

However, I do not remember how I did this.

What I would like: you start with the = sign and then you click on what should be added: values from the document I am in plus values from another document.

Just plus, plus, plus.

In Windows 7, which had Excel Starter in it, this is how it worked.

Also I would like the permission to update references to be given automatically.
Last edited by Léon on Fri Aug 19, 2022 4:10 pm, edited 1 time in total.
m3city
Posts: 7
Joined: Wed Aug 17, 2022 10:44 am

Re: Cell values

Post by m3city »

Hi,
copy-paste reference is just a method to obtain that specific entry in cell formula. Once you do it (just one time), then you can alter it in any way, without need to copy - you just take what it has in formula and reuse in any other cell, copied cells can be removed. The effect is exactly the same as you have - maybe you did copy it looong time ago by accident? ;)

Edit: after reading your answer once again I got what you asking for - you would like to start entering formula and click cells that you want to operate on. Just indicate cell A1 in one spreadsheet plus A1 in the other spreadsheet. Short answer - you cant do it. But copy-paste reference does this job by presenting you the reference in formula bar, that you can reuse in your actual calculation and then delete the copied cells. Did I got it right?
User avatar
Léon
Posts: 58
Joined: Thu Dec 17, 2020 10:22 pm
Location: Netherlands

Re: Cell values

Post by Léon »

Hello m3city,

Plus, plus, plus (the easy way) I can probably forget.

Yesterday I saw a topic below here called "External reference". In it, Mr. SuperTech refers to the PlanMaker manual. And that is very much in line with what you are saying.

But I do not get the reference as in my own image from the previous post.

By the way, this reference is less than a year old. I created it on 01 September 2021, I suddenly remembered. Clearly in my PlanMaker period, because I have been with PlanMaker for several years, in great gratitude, by the way.

I now get this: C:\users\, instead of just the name of my own document as in the image in my previous post.

Image

If I copy something from this specially pasted reference I just get =E1+E2, and if I delete the specially pasted reference, nothing at all remains. Probably I should not delete it.

In short: I don't quite understand it yet.
m3city
Posts: 7
Joined: Wed Aug 17, 2022 10:44 am

Re: Cell values

Post by m3city »

Hi,
that extended path in cell formula is expected when files are saved in different directories.
ksnip_20220819-231737.png
ksnip_20220819-231737.png (8.53 KiB) Viewed 4738 times
Note that you can use this reference (in my case 'C:\Users\KMJ\Desktop\[na pulpicie.pmdx]Sheet1'!G7) anywhere - in my case I originally pasted it cells in column A, but then used in cell I7 and added 50 to it, then dragged this formula two cells below.. You don't have to preserve cells pasted in first step. By the way I noticed that change of origin cells (ones from reference) changes the values in the destination spreadsheet automatically and instantly as long as both files are opened.

The origin file looks like this in my case:
ksnip_20220819-232540.png
ksnip_20220819-232540.png (4.04 KiB) Viewed 4738 times
User avatar
Léon
Posts: 58
Joined: Thu Dec 17, 2020 10:22 pm
Location: Netherlands

Re: Cell values

Post by Léon »

Hello m3city,

All my relevant PlanMaker documents are in one and the same folder called "Daily" (I use them every day).

Therefore, I do not understand this path and in my first image you do not see a path either.

But the important thing is that I now know how to create an external reference.

With path then. :-|

Thank you for your wise advice!
Post Reply

Return to “FreeOffice PlanMaker 2021 for Windows”