| Author |
 Topic  |
|
|
AKazak
Russia
1205 Posts |
 Posted - 06/30/2022 : 05:36:58 AM
|
OriginPro 2022b (64-bit) SR1 9.9.5.167
Windows 7 Pro SP1 x64
Greetings!
I want to create a user parameter with a formula that takes Long Name from the same column and returns results of vertical lookup from a different sheet?
Can you share a good example of this, please?
---
Andrey |
|
|
YimingChen
1664 Posts |
Posted - 06/30/2022 : 09:37:55 AM
|
=list(wcol(j)[L], book2_B)
the formula finds the value of the column longname in [book2]1!col(B), and returns the row index.
James |
Edited by - YimingChen on 06/30/2022 09:42:23 AM |
 |
|
|
AKazak
Russia
1205 Posts |
Posted - 06/30/2022 : 2:20:39 PM
|
quote:
Originally posted by YimingChen
=list(wcol(j)[L], book2_B)
the formula finds the value of the column longname in [book2]1!col(B), and returns the row index.
James
Dear James,
Thank you for the suggestion.
I want to use lookup function. How do I treat Categorical columns at the lookup reference sheet?
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 06/30/2022 : 2:27:19 PM
|
quote:
Originally posted by AKazak
I want to use lookup function. How do I treat Categorical columns at the lookup reference sheet?
---
Andrey
The following code seems to work fine:
=Lookup($(wcol(j)[L]), Sheet1!A, Sheet1!B)$
However I noticed that the formula in a User Parameter doesn't not update automatically if I alter Long Name values.
Is there a way to turn automatic updates on?
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/01/2022 : 12:08:53 AM
|
Why does the user parameter formula =$(wcol(j)[L]) returns "--" if the corresponding long name contains a string instead of a number?
Thank you.
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
|
|
YimingChen
1664 Posts |
Posted - 07/01/2022 : 10:33:40 AM
|
you need substitution notation %(wcol(j)[L]$) for text. try the line below:
=Lookup(%(wcol(j)[L]$), Sheet1!A, Sheet1!B)$
|
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/01/2022 : 11:37:09 AM
|
quote:
Originally posted by YimingChen
you need substitution notation %(wcol(j)[L]$) for text. try the line below:
=Lookup(%(wcol(j)[L]$), Sheet1!A, Sheet1!B)$
Hasn't got your point.
If I put %(wcol(j)[L]$) to LR formula, then it returns "--" for all the LR cells (see the screenshot above).
What LR formula will return long names of the corresponding columns?
---
Andrey |
|
|
|
YimingChen
1664 Posts |
Posted - 07/01/2022 : 11:54:25 AM
|
Because the cell asks for a text, and =wcol(j)[L]$ is already a text, so no need of substitution notation. Or you can put ="%(wcol(j)[L]$)", which is equal to ="AA".
James |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/01/2022 : 12:18:50 PM
|
quote:
Originally posted by YimingChen
Because the cell asks for a text, and =wcol(j)[L]$ is already a text, so no need of substitution notation. Or you can put ="%(wcol(j)[L]$)", which is equal to ="AA".
James
Not sure I've got your point.
Can you share a sample project, please?
---
Andrey |
|
|
|
YimingChen
1664 Posts |
|
|
AKazak
Russia
1205 Posts |
|
|
YimingChen
1664 Posts |
Posted - 07/01/2022 : 1:50:17 PM
|
Then there is a bug. Please use =wcol(j)[L]$. I don't see it's not updating. You may need to click the refresh button.
James
|
Edited by - YimingChen on 07/01/2022 1:57:49 PM |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/01/2022 : 2:45:46 PM
|
quote:
Originally posted by YimingChen
Then there is a bug. Please use =wcol(j)[L]$. I don't see it's not updating. You may need to click the refresh button.
James
Dear James,
If I use =wcol(j)[L]$ as LR formula, then Origin does not accept it and leaves LR empty. Seems to be as a bug...
---
Andrey |
|
|
|
YimingChen
1664 Posts |
|
|
AKazak
Russia
1205 Posts |
Posted - 07/02/2022 : 01:15:32 AM
|
quote:
Originally posted by YimingChen
To me RL formula (=wcol(j)[L]$) works but RL Cell (wcol(j)[L]$) doesn't work. Is that the same case for you? See the image below and the attached project file.
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution_YM.opju
James
I see a misunderstanding here.
In my system:
if I put "=wcol(j)[L]$" in RL cell(s) manually (via cell edit), the it works as expected;
if I put "wcol(j)[L]$" in RL cell(s) manually (via cell edit), the cell(s) display "wcol(j)[L]$" as regular text as expected, since equal character "=" is missing.
This is fine.
My goal is to design RL formula and paste it via Edit GUI dialog, but it fails:
By the way, I noticed that LR formulas seem not to update automatically.
How do I trigger automatic updates of the LR formulas?
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/07/2022 : 06:18:24 AM
|
Do you reproduce the issue?
---
Andrey |
|
|
|
YimingChen
1664 Posts |
Posted - 07/07/2022 : 08:47:19 AM
|
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
To trigger the update, you may need to click the Refresh toolbar button or press F5.
James |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/08/2022 : 01:56:28 AM
|
quote:
Originally posted by YimingChen
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
To trigger the update, you may need to click the Refresh toolbar button or press F5.
James
Dear James,
Thank you for the suggestion.
Please ask the developer to fix the issue in the next beta version.
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
|
|
YimingChen
1664 Posts |
Posted - 10/31/2022 : 12:13:44 PM
|
I will report to fix it. Can you use the syntax below instead?
=lookup(wcol(j)[D1]$,Book1_B,Book1_X)$
Here is a better solution by removing the quotation marks, try below:
=lookup(This[SID]$,Data!B,Data!X)$ |
Edited by - YimingChen on 10/31/2022 3:18:33 PM |
|
|
|
cpyang
USA
1406 Posts |
Posted - 10/31/2022 : 8:14:19 PM
|
wcol(j) should not be used in cell formula, also, quoting sheet name should not be an issue, please try
=lookup(This[SID]$,"Data"!B,"Data"!X)$
wcol(j) is for using in multiple column formula where j will be properly incremented.
CP
|
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/31/2022 : 10:40:17 PM
|
quote:
Originally posted by cpyang
wcol(j) should not be used in cell formula, also, quoting sheet name should not be an issue, please try
=lookup(This[SID]$,"Data"!B,"Data"!X)$
wcol(j) is for using in multiple column formula where j will be properly incremented.
CP
Thank you for clarifying this.
Where do I read about pre-defined variables, including This?
In particular, what does This[SID]$ mean?
---
Andrey |
Edited by - AKazak on 11/01/2022 01:01:19 AM |
|
|
|
minimax
357 Posts |
|
|
AKazak
Russia
1205 Posts |
|
|
minimax
357 Posts |
Posted - 11/02/2022 : 01:08:18 AM
|
quote:
Is below the correct syntax for lookup to return numeric values instead of strings?
Yes.
quote:
Is this allowed to use Substitution Notation in column formulae
It should allow. |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 11/02/2022 : 02:14:04 AM
|
quote:
Originally posted by minimax
quote:
Is this allowed to use Substitution Notation in column formulae
It should allow.
In my system the column formula:
=%(lookup(This[SID]$,"Data"!B,"Data"!X)$)
doesn't work.
---
Andrey |
|
|
|
minimax
357 Posts |
Posted - 11/02/2022 : 03:03:09 AM
|
%(str$) requires a variable, but yours is a function return value.
I guess it does not work. |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 11/02/2022 : 03:50:16 AM
|
quote:
Originally posted by minimax
%(str$) requires a variable, but yours is a function return value.
I guess it does not work.
Got it!
---
Andrey |
|
|
|
lagaaduu
1 Posts |
Posted - 11/23/2022 : 05:30:17 AM
|
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
|
|
|
| |
Topic |
|
User Parameters formula over Column Label Row data
