The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
 All Forums
 Origin Forum
 Origin Forum
 Addition of multiple sheets from different book

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:
UserName:
Password:
Anti-Spam Code:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkUpload FileInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
Check here to subscribe to this topic.
   

T O P I C    R E V I E W
cts18488 Posted - 06/17/2022 : 12:27:42 PM
Origin Ver. 2021b and Service Release (Select Help-->About Origin): SR2
Operating System: Windows 10 enterprise

Hi,

Let's suppose I have 2 books: Book1 and Book2 in which I have the data in columns B to M (in both books but different data). There is another Book 3 where I want to have the sum of the columns of Book1 and Book2. I am aware that if I use in the F(x) [book1]1!B + [book2]1!B (in column B of Book3) that will do the addition, but that will become a long process and prone to mistake if I need to repeat for all the columns and especially if I have multiple tasks like this.

So, my question is: is it possible to fill the first column (column B) and then drag with the mouse across the other columns which then will be filled automatically ([book1]1!C + [book2]1!C for column C and so forth and so on) - I know that in Excel I can do that, but I want to know if Origin is able to do that. If not are there other alternatives?

Thanks,
Tibi

4   L A T E S T    R E P L I E S    (Newest First)
snowli Posted - 06/20/2022 : 09:38:31 AM
My colleague found this also works
[book1]1!$(j)+$(32980)

Thanks, Snow
snowli Posted - 06/20/2022 : 09:15:37 AM
32980+[book1]1!$(j) will work.

The other day, to solve the original issue, i had thought of using
[book1]1!2+[book2]1!2 in Fx of column B in Book3.
Then drag to right.
-> [book1]1!2+[book2]1!3, -> [book1]1!2+[book2]1!4, ... So it seems we only add last part if numeric.

We created ORG-25395 to see if developer can improve auto fill of column formula.

Thanks, Snow
cts18488 Posted - 06/20/2022 : 06:38:33 AM
Thanks. It works.

What to do if I want to use a constant instead? Instead of using [book1]1!$(j)+[book2]1!$(j), I want to use [book1]1!$(j)+32980 in all columns. But if I use this formula in column B and drag along the Fx in the rest of the columns the constant (32980) will increase with one unit per columns.

Tibi



quote:
Originally posted by snowli

Hello,

We can do what excel does which is cell formula.
E.g. i type =Sheet1!B1+Sheet2!B1 in the new sheet3's B1 cell.
Then drag it downwwards once to fill col B.
Then I need to drag rowwise to fill all other columns since excel doesn't support diagonal drag to autofill.

Same in Origin, if u enter =[book1]1!B1+[book2]1!B1 in B1 cell of Book3's sheet1.
Then u can drag diagonally to end of M column. It will fill correctly as well.
But if there are many data in a sheet and using cell formula, it can cause slowness.


To do column formula in Origin and autofill, try this
Enter [book1]1!$(j)+[book2]1!$(j) as column B's formula. Note: j means the column index of the column

Then drag the fx formula rowise to autofill other column.


Thanks, Snow





snowli Posted - 06/17/2022 : 1:38:09 PM
Hello,

We can do what excel does which is cell formula.
E.g. i type =Sheet1!B1+Sheet2!B1 in the new sheet3's B1 cell.
Then drag it downwwards once to fill col B.
Then I need to drag rowwise to fill all other columns since excel doesn't support diagonal drag to autofill.

Same in Origin, if u enter =[book1]1!B1+[book2]1!B1 in B1 cell of Book3's sheet1.
Then u can drag diagonally to end of M column. It will fill correctly as well.
But if there are many data in a sheet and using cell formula, it can cause slowness.


To do column formula in Origin and autofill, try this
Enter [book1]1!$(j)+[book2]1!$(j) as column B's formula. Note: j means the column index of the column

Then drag the fx formula rowise to autofill other column.


Thanks, Snow




The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000