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
Username:
 Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum
 Origin Forum
 Addition of multiple sheets from different book		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

cts18488

United Kingdom
85 Posts
Posted - 06/17/2022 :  12:27:42 PM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
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

snowli

USA
1411 Posts
Posted - 06/17/2022 :  1:38:09 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
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
Go to Top of Page

cts18488

United Kingdom
85 Posts
Posted - 06/20/2022 :  06:38:33 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
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
Go to Top of Page

snowli

USA
1411 Posts
Posted - 06/20/2022 :  09:15:37 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
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
Go to Top of Page

snowli

USA
1411 Posts
Posted - 06/20/2022 :  09:38:31 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
My colleague found this also works
[book1]1!$(j)+$(32980)

Thanks, Snow
Go to Top of Page

AmandaLu

439 Posts
Posted - 11/11/2022 :  01:01:10 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

Origin 2023 is available!

Following issue you suggested in this post has been implemented in Origin 2023:
Autofill for Column Formula (ORG-25395).

Right now, you can drag and drop a column formula cell to autofill something like
[book1]1!B+[book2]1!B, [book1]1!C+[book2]1!C, [book1]1!D+[book2]1!D, …

Please download Origin 2023 from the link below, install and try it.
http://www.originlab.com/demodownload.aspx

If you do not have an active Origin Maintenance, you can still use this trial version for 7 days.

We are looking for your feedback! Any suggestion will be appreciated!

Thanks,
Amanda
OriginLab Technical Service
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000