Cell values
Cell values
Is it possible to enter data from another document in a formula in which you add cell values from various worksheets?
Re: Cell values
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
column C in second file has static values of 1, 2, 3
Then I copy that column C3:C5 and Paste Special in the first file in cell B4, but select "Paste reference"
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.
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 (8.93 KiB) Viewed 9816 times
- ksnip_20220817-213305.png (2.62 KiB) Viewed 9816 times
='[second file.pmdx]Sheet1'!C3
If you use ribbon interface, then you can go to Data - External references to manage that connection between files.
Re: Cell values
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.
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.
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.
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.
Re: Cell values
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?
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?
Re: Cell values
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.
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.
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.
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.
Re: Cell values
Hi,
that extended path in cell formula is expected when files are saved in different directories.
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:
that extended path in cell formula is expected when files are saved in different directories.
- ksnip_20220819-231737.png (8.53 KiB) Viewed 9773 times
The origin file looks like this in my case:
- ksnip_20220819-232540.png (4.04 KiB) Viewed 9773 times
Re: Cell values
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!
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!