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
 2023bSR1: Column Formula to summarize data from wk		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

AKazak

Russia
1205 Posts
Posted - 11/21/2023 :  07:37:46 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
OriginPro 2023b (64-bit) SR1 10.0.5.157
Windows 10 Pro for Workstations 10.0.19045.3570

Greetings!

I have many workbooks with multiple sheets in a folder

How can I design a column formula to collect the data from different worksheets using the following setting of the summary sheet:


To my current understanding Origin doesn't offer a data summary tool to do this type of summary (most probably I am wrong).

Thank you.

---
Andrey

YimingChen

1613 Posts
Posted - 11/21/2023 :  08:47:23 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
You can define a Labtalk function with the first argument as the book name and the second argument as the sheet name. The function should return a summary of the sheet.
https://www.originlab.com/doc/en/LabTalk/guide/Functions#User-Defined_Functions

James
Go to Top of Page

YimingChen

1613 Posts
Posted - 11/21/2023 :  09:11:55 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
You may enter a cell formula like below in cell C1, then drag to autofill the cells below.
=="["+A1$+"]"+B1$+"!"+C[Column]$+C[Row]$


See the reference page:
https://www.originlab.com/doc/Origin-Help/Using-Formulae-to-Set-Cell-Values#Extending_Sheet_Part

Yiming
Go to Top of Page

AKazak

Russia
1205 Posts
Posted - 11/21/2023 :  10:43:20 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Dear James and Yiming,

Thank you very much for sharing this information.
This exactly what I was asking.

---
Andrey
Go to Top of Page

AKazak

Russia
1205 Posts
Posted - 11/22/2023 :  07:11:53 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

You may enter a cell formula like below in cell C1, then drag to autofill the cells below.
=="["+A1$+"]"+B1$+"!"+C[Column]$+C[Row]$


See the reference page:
https://www.originlab.com/doc/Origin-Help/Using-Formulae-to-Set-Cell-Values#Extending_Sheet_Part

Yiming


Dear Yiming,

This formula return "--" in my case.

---
Andrey
Go to Top of Page

YimingChen

1613 Posts
Posted - 11/22/2023 :  2:32:33 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Could you check the attached project file? Probably because you have used "Y" as the column name.

https://my.originlab.com/ftp/forum_and_kbase/Images/Cell%20Range%20Notation.opju

James
Go to Top of Page

AKazak

Russia
1205 Posts
Posted - 11/23/2023 :  05:43:46 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

Could you check the attached project file? Probably because you have used "Y" as the column name.

https://my.originlab.com/ftp/forum_and_kbase/Images/Cell%20Range%20Notation.opju

James


Dear James,

Please see the attached sample project saved using OriginPro 2024 (64-bit) 10.1.0.170:
https://my.originlab.com/ftp/forum_and_kbase/Images/2023-11-23.opju

What is the current issue with the formula?

---
Andrey
Go to Top of Page

AKazak

Russia
1205 Posts
Posted - 11/27/2023 :  07:43:26 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Dear James,

Have you managed to reproduce the odd behavior?

Thank you.

---
Andrey
Go to Top of Page

YimingChen

1613 Posts
Posted - 11/27/2023 :  08:54:56 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Because your sheet name contains a minus sign, leading to misinterpretation. Please enclose the sheet name in double quotes. Try below:

=="["+A1$+"]"+char(34)$+B1$+char(34)$+"!"+C[Column]$+C[Row]$


James

quote:
Originally posted by AKazak

Dear James,

Have you managed to reproduce the odd behavior?

Thank you.

---
Andrey
Go to Top of Page

AKazak

Russia
1205 Posts
Posted - 11/28/2023 :  07:49:08 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

Because your sheet name contains a minus sign, leading to misinterpretation. Please enclose the sheet name in double quotes. Try below:

=="["+A1$+"]"+char(34)$+B1$+char(34)$+"!"+C[Column]$+C[Row]$


James



Dear James,

It works like a charm now!
Very well done.

Can you share an URL to the help page describing book and sheet naming limitations, please?

---
Andrey
Go to Top of Page

ChaoC

USA
178 Posts
Posted - 11/28/2023 :  10:07:53 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by AKazak


Can you share an URL to the help page describing book and sheet naming limitations, please?



Please see:
https://www.originlab.com/doc/Origin-Help/Naming-Conventions
https://www.originlab.com/doc/Origin-Help/Origin-Limitation

Best,
Chao
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