| Author |
 Topic  |
|
|
ru4n0907
Brazil
6 Posts |
 Posted - 10/07/2022 : 09:07:02 AM
|
| hello, I'm working with several sheets at the same time and I would like a formula in the script window to make the account in all of them at once. what I want is to calculate the ratio between two columns in these sheets and put it in another column and then get a specific value from that other column, for example: get the value of row 10 of that column where we calculated the ratio and put it in a new column . So I want a formula that does this in all sheets but 1, which would be, for example, the sheet ''values'' |
|
|
snowli
USA
1429 Posts |
Posted - 10/07/2022 : 09:36:35 AM
|
You can use Set Column Values dialog (F(x) cell) of a column to enter formulas to do column based calculation.
https://www.youtube.com/watch?v=vAghAi0dazM
E.g. in a column, u can enter [book1]1!B/[book2]1!B to calculate ratio between column Bs of book1's 1st sheet and book2's 1st sheet, etc.
U can also use cell formula
e.g. =[Book1]1!B[10] to refers to 10th row of column B in 1st sheet of Book1.
Maybe you can share a project so we can better understand your question.
Thanks, Snow
|
 |
|
|
ru4n0907
Brazil
6 Posts |
Posted - 10/07/2022 : 09:57:34 AM
|
| so, I have 100 other books to do this account in all of them, but manually it takes time to put the formula in one by one after getting the value of a certain row of the column, I want some command to do this in all of one time |
|
|
|
snowli
USA
1429 Posts |
|
|
ru4n0907
Brazil
6 Posts |
Posted - 10/07/2022 : 11:40:45 AM
|
solved it in part, how do I get 1 woorkbook out of the loop?
and then how do i pass the calculated results to another woorkbook? I'm using this:
doc -ef W
{
// Loop over all worksheets in each workbook
loop(ii,1,page.nlayers)
{
range rW = [%H]$(ii)!;
rW.addCol(Result);
newColIndex = rw.nCols;
range rA = [%H]$(ii)!col(3);
range rB = [%H]$(ii)!col(4);
range rF = [%H]$(ii)!col($(newColIndex));
range rR = [%H]$(ii)!col($(newColIndex));
rR = rA / rB;
}
} |
|
|
|
snowli
USA
1429 Posts |
Posted - 10/07/2022 : 12:20:25 PM
|
Do you mean you want to exclude one book in the loop?
U can wrap
if(%H != "Book4")
{
}
around
loop(ii,1,page.nlayers)
{
}
U can use Worksheet: Append Worksheet... to pick sheets and specified columns to put them into a new book.
Thanks, Snow
|
|
|
| |
Topic |
|
|
|
Multiple sheets - script window formula