| Author |
 Topic  |
|
|
RandomOUser
5 Posts |
 Posted - 06/15/2020 : 09:14:02 AM
|
Hey Origin community,
I have a question concerning OriginPro 2020. In order to combine data from multiple workbooks and worksheets into a single sheet for further processing, I use cell formulas to access values.
As an example, I might type =[Book1]Sheet1!B1 into column A1 of a sheet to access cell B1 of Sheet1 in Book1.
In some cases, I would now like to "build" the references to different books and sheets using variables. As an example, I would like to replace Book1 in the aforementioned cell formula with a formula referencing a certain cell containing the corresponding workbook name as a string (let's say B3) in the active sheet. I tried subsituting Book1 with B3$ (obtaining =[B3$]Sheet1!B1 as a cell formula) / $(B3) / %(B3) / =B3$ etc. but nothing seems to work. I guess I don't understand the substitution notation correctly...
Can anyone help me?
Thank you for your help! |
Edited by - RandomOUser on 06/15/2020 09:17:06 AM |
|
|
Chris D
428 Posts |
|
|
RandomOUser
5 Posts |
Posted - 06/15/2020 : 12:24:53 PM
|
First of all thank you for your help. Doesn't seem to work for some reason. If I enter your formula into an arbitrary cell after having defined the variable through the command window, all commas are replacedc by dots (might this have to do with european decimal point settings?) and I get no return value.
But anyways: If I understand you correctly, there is no way to achieve what I want without first defining a variable? I.e. it is not possible to avoid having to use separate scripts? That would be rather unfortunate because it would defeat the purpose of what I am triying to do. The idea is to extract book and sheet names from cell values without taking the detour of defining a variable.
Thanks again!
Edit: I have played around a bit and it seems that setting the cell formula to =[%(col(B)[3]$)]Sheet1!A1 does the trick. |
Edited by - RandomOUser on 06/15/2020 1:07:56 PM |
 |
|
|
Chris D
428 Posts |
Posted - 06/15/2020 : 1:19:26 PM
|
Let's see if I understand it okay.
If this value is in the current worksheet in column B, row 2:
Book3
And I put this cell formula in the same sheet somewhere:
=%([B2$]Sheet1, 2, 1)
It will fill the cell with the value from Book3, Sheet1, column B, row 1.
Is that what you want?
Thanks,
Chris Drozdowski
Originlab Technical Support
|
|
|
|
RandomOUser
5 Posts |
Posted - 06/15/2020 : 4:21:57 PM
|
Thanks for your swift reply. Yes, this is exactly what I want to do. However, if I copy and paste =%([B2$]Sheet1, 2, 1)
to an arbitrary cell (of course making sure that the book / sheet / cell referenced actually exist and having the value Book3 in cell B2), I only get an "unknown function" error and the cell contents is automatically changed to =%([B2$]Sheet1. 2. 1) for whatever reason. I use OriginPro 2020 (64-bit) SR1 9.7.0.188 (Academic) on Windows 10, if that helps.
What does work, however, is using =[%(col(B)[2]$)]Sheet1!A1 instead. I have no idea why your code doesn't work. It would be much cleaner than my version.
quote:
Originally posted by Chris D
Let's see if I understand it okay.
If this value is in the current worksheet in column B, row 2:
Book3
And I put this cell formula in the same sheet somewhere:
=%([B2$]Sheet1, 2, 1)
It will fill the cell with the value from Book3, Sheet1, column B, row 1.
Is that what you want?
Thanks,
Chris Drozdowski
Originlab Technical Support
|
Edited by - RandomOUser on 06/15/2020 4:23:56 PM |
|
|
| |
Topic |
|
|
|
Using Substitution in Cell Formulas