[FIXED] How to specify a range in a Google Sheets formula, where the end row number is a reference to another cell?

Issue

I have several google spreadsheets with different number of records (rows) - let's say

file 1: 200.000 records (rows)

file 2: 350.000 records (rows)

file 3: 246.000 records (rows)

etc.

I use a lot of formulas (20-30) that reference entire columns from file 1:

sumif(a$2:a$200000,">3") countif(b$2:b$200000, "=n") etc.

I want to reuse the already created formulas for the other files, but since the number of records there is different, I would have to replace the 200.000 with 350.000 for file 2 in 20-30 cells, with 246.000 for file 3 in 20-30 cells etc.

That would be too much work.

Is there a way to specify the end point of the range not with a constant but by pointing to a cell that contains the number of rows? e.g.

I would add in cell z1 the number of rows: 200000

The other formulas would contain something like

sumif(a$2:a$ (something that tells sheets to use as row number the number from z1) )

This way I would need to only replace the number in z1, and all formulas would be updated correctly. Any ideas?

I tried using indirect:

="a"&indirect("z1")

where z1 contains 200000

This pastes

a200000

But if I try using it in a range, it's not recognized as a range

=sum(a1:"a"&indirect("z1"))

Any ideas how to do that correctly?

Solution

why not just skip it... instead of:

=sumif(a$2:a$200000,">3")

use:

=sumif(a$2:a,">3")

to answer your indirecting, the correct syntax would be:

=sum(INDIRECT("a1:a"&z1))

Answered By - player0

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How can I drag down to multiple rows a formula which has more than 1 row in its result?

Issue

I have an extension I am getting the data from, and I am referring to that extension in a formula with result of pre determined rows but I want more than 1 row in its result and that's when I have this problem, because I want to drag the formula to multiple rows but they overlap each other, for example if I wanted 3 rows in the result of the formula starting in row 1 and then drag it down from row 1 to row 3 the formula in row 1 and 2 will show an error because they're overlapped in each other I will put a picture in how it looks...

Is there a way to specify amount of rows as a space between each formula in a way that when I drag the formula down to more rows it will adjust to the "space" I specified?

This is the formula I am using, I am also referring to another sheet as you can see so it'll be great if you can use this formula to answer my question, if I can specify the "space" using another formula that is (also it's probably obvious but the pre determined rows in the formula is the "2d").

=CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d")

Solution

this is usually solved by constructing an array of formulae where you stack them up in the line like:

={CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C5&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C6&"/USD", "price_history", "2d")}

this way the 2nd fx will pick up right after 1 fx ends

you can ease your pain of a "hand job" from constructing such an array - especially if that array needs to span over the larger range - by building a formula to generate a formula. for example: https://stackoverflow.com/a/68278101/5632629

also, make sure you obey the law of array constructs and successfully avoid all array errors - https://stackoverflow.com/a/58042211/5632629

Answered By - player0

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How can I write a formula that will automatically take value from another subsheet in Google Sheet?

Issue

For ex, we have 10 list of item with price which occupy 10 rows. and then in the total section we write something like this

=SUM(A1:A11)

So, it will give a total value of all 10 item's price. Now let's say after this formula, if I make any change, let's say I changed 5th row price to some diff amount, in that case google sheet is IMMEDIATELY amd automatically making change to our total value.

The similar behviour I want is. From same Google sheet with different SUB-SHEET, if I make any change, then that value I want it to reflect in my current subsheet in a particular position. Kind off automatically copy/paste to my current sheet, when I make change in my diff subsheet.

Is that possible?

Example sheet: https://docs.google.com/spreadsheets/d/168a1b2GAo-X4iw4-b18N9z7ROloToiUtWQUXnzp79zU/edit?usp=sharing

So from "Sheet A" I want to paste value to "Sheet B" everytime I make change in "Sheet A"

Solution

you can try like this:

='Sheet A'!A13

if sub sheet name does not contain space you can omit single quotes ':

=SheetA!A13

the same way you can use:

=SUM('Sheet A'!A2:A12)

Answered By - player0

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] how to apply conditional formatting to a named range?

Issue

I'm searching for a way in google sheets to use a named range, or preferably by referring to a cell using INDIRECT() to specify the range to which the formatting should be conditionally applied.

My approach has been to create a formula in cell A1 which specifies a range, say f1:k10, but which can change dynamically. When I enter =indirect(A1) in the box "Apply to range" then I get the error message "Please enter a valid range"

This tells me that Google Sheets doesn't allow named or indirect ranges as the target range which I want to format. I'm hoping that I'm wrong and that there is a nice simple solution?...

I've tried:

=indirect(a1)
=indirect ($a$1)
indirect(a1)
=range-name
range-name

I've also spent a couple of hours trawling google for an answer

Ideally, when I specify a named range or refer indirectly to a range then I would expect that the specified range will be formatted in accordance with the criteria that I am able to set up in the format rules.

Solution

nope, you are right, Google Sheets does not support referenced ranges in Apply to range field.

kind of same as here:
Is it possible to assign conditional formatting to a named range in Google Sheets?

Answered By - player0

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] How to stack multiple columns into one, alternating rows - Google Sheets

Issue

I'm working on a Google Sheets document that has data stacked in columns in an unusual order (see image for reference)

I have what you see on columns A, B and C. But I want what I have on column E. I have 8 pages with hundreds of data elements, all sorted in 3 columns like that and I'm looking for a formula or a custom script that would allow me to arrange the data in the right sequence.

I did research trying to find a solution, but none of the ones I found work well for this situation. The order of the elements is very important.

---

EDIT: The data is not numbers. It's text. So sorting formulas wouldn't work, because it would sort it alphabetically and it would mess up the order

Solution

all you need is:

=FLATTEN(A1:C7)

with open ranges you can do:

=QUERY(FLATTEN(A1:C); "where Col1 is not null"; )

see: https://stackoverflow.com/questions/73767719/

Answered By - player0

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How to apply multiple conditions from one cell for SUMIF | GoogleSheets

Issue

Do you have an idea for a function that would sum the amounts from table 2 based on Unique_nr from Table 1?

I tried to do it this way:

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{SPLIT(A3,",")},F3:F9))) <---doesn't work

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{"8-1","9-1"},F3:F9))) <----It works

Theoretically the SPLIT() function gives the same result as I type manually, but unfortunately it doesn't work.

I would like to do this with one function for the entire range of data

https://docs.google.com/spreadsheets/d/1JGvFIZIE6c_D0A2Z4xCWf7pxqVft4Zsb6S-45_d9LY4/edit?usp=sharing

Solution

You were almost in the correct way

First of all, remove the double quotes from the cells.

By default SPLIT will Divide text around a specified character or string and it means there will be an extra step in order to use this output to another function, it's possible that your cell had an extra character and the TRIM function will solve it.

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{trim(SPLIT(A3,","))},F3:F9)))

You can use VLOOKUP and SUM as a different approach

As you mentioned SPLIT is a good approach to treat comma-separated cells. In order to avoid unexpected spaces TRIM is a good option (it's optional) as well as IFNA in order to fill that cell in case there's not a match.

=ArrayFormula(SUM(IFNA(vlookup(trim(split(A3,",")),E3:F9,2,0))))

Answered By - Jose Vasquez

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] Why is it faster in Google Sheets to split this formula into two steps?

Issue

Edit: I initially diagnosed this problem totally wrong, so the question is entirely rewritten to reflect new understanding.

The problem can be reproduced using a Google spreadsheet with one sheet that contains one header row and a significant number of additional rows (let’s say 5,000).

I wanted column A to increment by 1, starting with A2, as long as the adjacent cell in B was not blank. I used this formula in A1:

={"SKU"; arrayformula(if($B2:$B="","",text(row($A2:$A),"000000")))}

This formula worked but caused extremely significant lag.

In one of my attempts to resolve the issue, I added a helper column before column A and split my formula into two formulas to see which function was causing the lag:

Cell A1: ={"SKU (helper)"; arrayformula(if($C2:$C="","",row($A2:$A)))}

Cell B1: ={"SKU"; arrayformula(if($C2:$C="","",text($A2:$A,"000000")))}

To my surprise, the answer was neither. The lag was completely eliminated. What is the reason? And is it possible to eliminate the lag without the need for a helper column?

Solution

use:

={"SKU"; SEQUENCE(ROWS(A:A)-5344; 1; 5344)}

---

update:

={"SKU"; INDEX(TEXT(SEQUENCE(COUNTA(B2:B)), "000000"))}

if you have empty cells in between use:

=LAMBDA(x, {"SKU"; INDEX(IF(x="",,
 TEXT(COUNTIFS(x, "<>", ROW(x), "<="&ROW(x)), "000000")))})
 (B2:INDEX(B:B, MAX((B:B<>"")*ROW(B:B))))

Answered By - player0

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How detect is cell merged using PhpSpreadsheet

Issue

Using PhpSpreadsheet, when I read data from the xls table, I need to find out whether this cell is merged with others, if yes, then do certain actions with it, if not, then do nothing

at the moment I thought of only checking for the presence of empty array elements after the text cell, but this solution is not quite universal ...

...
$inputFileName = $_FILES['uploadfile']["tmp_name"];
echo 'TMP-FILE-NAME: ' . $inputFileName;

$spreadsheet = IOFactory::load($inputFileName); //create new speedsheen object
$loadedSheetNames = $spreadsheet->getSheetNames(); //get name of Sheet

//and than print it
    //get Sheet Name
foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {

  $sheet = $spreadsheet->getSheet($sheetIndex);
  echo "<table border=\"1\">";
  $rows = $sheet->toArray();
   **$mergeCell = $sheet->getMergeCells(); // - This is the answer to my question**
foreach ($rows AS $row) {
echo "<tr>";
    foreach ($row AS $cell) {
        echo "<td>" . $cell . "</td>";
    }

    }
     echo '<br/>';
}
 echo "</table>";

Solution

In order to check the cell was merged or not

1. First, you can use getMergeCells function to get all merged cells.

2. Then do loop in that cells list to check your cell is in or is not in that list.

Summarize: You can use this function to check cell merged or not

// Check cell is merged or not
function checkMergedCell($sheet, $cell){
    foreach ($sheet->getMergeCells() as $cells) {
        if ($cell->isInRange($cells)) {
            // Cell is merged!
            return true;
        }
    }
    return false;
}

The code was referenced from this answer

For PhpSpreadSheet:

• getMergeCells: https://phpoffice.github.io/PhpSpreadsheet/1.2.0/PhpOffice/PhpSpreadsheet/Worksheet/Worksheet.html#method_getMergeCells
• isInRange: https://phpoffice.github.io/PhpSpreadsheet/1.2.0/PhpOffice/PhpSpreadsheet/Cell/Cell.html#method_isInRange

Answered By - Ngoc Nam

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How to reference all cells from a cell to the very bottom and right in Google Sheets?

Issue

I know you can reference cells in these ways:

Address	Explanation
A1	The cell at A1
A2:A	All rows in column A starting from row 2
B1:1	All columns in row 1 starting from column B

What I can't find it how to reference all rows and columns starting at B2. Is that possible?

I am not opposed to using a formula but would prefer not to.

Solution

there are several ways one of wich is:

=INDIRECT("B2:"&ADDRESS(ROWS(A:A), COLUMNS(1:1))) 

or:

=OFFSET(B2,,,9^9,9^9)

Answered By - player0

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] Update user id by looking up two other tables

Issue

I have a table/sql issue I don't know how to solve.

I need to update/create a table of user ids with order ids.

Therefore I have to get a new user_id, by searching for the email in an old list. With the email adress I need to look up the new user id.

So the logic is like: order_id -> look at the old user_id -> look at the email -> look at the new user_id

I tried to create an example:

---------------------
TABLE: USERS_OLD (a list of user ids and an email adress)
id  email
1   test1@email.com
2   test2@email.com
3   test3@email.com
4   test4@email.com
---------------------

---------------------
TABLE: USER_ORDERS_OLD (the connection of an order id with a user id)
user_id     order_id
1           DLEFGM
2           OPDFGT
3           UZDFGP
4           POIDSX
---------------------

---------------------
TABLE: USERS_NEW (a new list of users id with the same emails from table USERS_OLD)
id  email
5   test1@email.com
9   test2@email.com
10  test3@email.com
17  test4@email.com
---------------------

What I want to create:
---------------------
TABLE: USER_ORDERS_NEW
user_id     order_id
5           DLEFGM
9           OPDFGT
10          UZDFGP
17          POIDSX
---------------------

I have no idea how to do that action. I don't even know what to search for.

What I managed to do is a LEFT JOIN sql statement to compare the user ids and create a list of matching user_ids. But I have no idea how to look up over even more tables...

Hopefully someone can help me. If it's easier I could also try to do it in spreadsheets.

Thanks in advance!

Solution

Assuming you just want to return the query then use;

SELECT u3.id, u2.order_id
FROM USERS_OLD u1
JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
JOIN USERS_NEW u3 ON u1.email = u3.email;

However, if you want to write the result into a new table, then you need to create the table first.

CREATE TABLE USER_ORDERS_NEW (user_id INTEGER, order_id VARCHAR(50));

INSERT INTO USER_ORDERS_NEW (user_id, order_id)
       SELECT u3.id, u2.order_id
       FROM USERS_OLD u1
       JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
       JOIN USERS_NEW u3 ON u1.email = u3.email;

See Demo

Answered By - Okechukwu Ossai

Read More