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 for Programming
 LabTalk Forum
 Column Formula to get data from other sheets		  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
1228 Posts
Posted - 07/15/2022 :  03:34:52 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
OriginPro 2023 (64-bit) Beta 1 10.0.0.47
Windows 7 Pro SP1 x64

Greetings!

I have sheet 1 and sheet 2.
Sheet 1 has data.
2!A and 2!B store row and column indices correspondingly.
I want to set 2!C to return data values from Sheet 1 based on the indices in 2!A and 2!B.

If I try the formula 
1!wcol(2)[3]
for 2!C , then it works correctly.

However if I try the formula 
1!wcol(A)[C]
, then I get "--". Moreover, if is try 
1!wcol(A[i])[C[i]]
, then I still get "--".

Can you explain the reason for this, please?
How do I achieve my goal?

Thank you.

---
Andrey

YimingChen

1684 Posts
Posted - 07/15/2022 :  10:05:06 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
wcol() function accepts column number. See the page:
https://www.originlab.com/doc/LabTalk/ref/WCol-func

James
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/15/2022 :  11:06:44 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

wcol() function accepts column number. See the page:
https://www.originlab.com/doc/LabTalk/ref/WCol-func

James


Dear James,

I understand.
How do I make A or A[i] to return the corresponding column values while using it in Column Formula?

---
Andrey
Go to Top of Page

YimingChen

1684 Posts
Posted - 07/15/2022 :  11:15:58 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
col(A)
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/15/2022 :  11:26:11 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

col(A)


The problem with col(A) is that I cannot specify sheet and cell:
1!col(A)[C]

doesn't work.

Column A stores the target column indeces.

Please see the attached OPJU:
https://my.originlab.com/ftp/forum_and_kbase/Images/wcol%202022-07-15.opju

---
Andrey
Go to Top of Page

YimingChen

1684 Posts
Posted - 07/15/2022 :  12:18:55 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
This works:

1!wcol(col(A)[i])[col(B)[i]]
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/15/2022 :  12:52:18 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

This works:

1!wcol(col(A)[i])[col(B)[i]]


OK.

---
Andrey
Edited by - AKazak on 07/18/2022 08:34:57 AM
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/18/2022 :  08:34:28 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

This works:
1!wcol(col(A)[i])[col(B)[i]]


This works, but delivers incorrect results.
Please check it out in the updated OPJU:
https://my.originlab.com/ftp/forum_and_kbase/Images/wcol%202022-07-18.opju

---
Andrey
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/18/2022 :  08:52:21 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Origin suggests in Smart Hint pop-up window:
quote:
Origin introduces Spreadsheet Cell Notation in workbook to make column formula easier.
Use A to refer to column A.
Use A1 instead of col(A)[1] for 1st cell of column A.
Use Sheet1!A to directly refer to column A in other worksheet.

If you continue to use Col(A) notation, formula will not update when moving/inserting/deleting columns.

Change notation mode by right clicking on book title and choosing Properties.
Click here to learn more.


I convert the suggested:
1!wcol(col(A)[i])[col(B)[i]]

to Spreadsheet Cell Notation:
1!wcol(A[i])[C[i]]


However the last line returns "--".
Why is that happening?

---
Andrey
Go to Top of Page

YimingChen

1684 Posts
Posted - 07/18/2022 :  09:55:49 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Yes, this is a bug. I have reported to jira ORG-25503.

James
Go to Top of Page

AKazak

Russia
1228 Posts
Posted - 07/18/2022 :  11:06:30 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by YimingChen

Yes, this is a bug. I have reported to jira ORG-25503.
James


For me the bug is critical.
Hope to test it fixed in the next Beta of Origin 2023.

---
Andrey
Go to Top of Page

minimax

362 Posts
Posted - 07/19/2022 :  01:24:24 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi AKazak,

We found wcol() has some restriction and not easy to improve.

Would you mind to try cell() function instead?

i.e.
1!cell(2!A[i],2!B[i])
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