CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
Gav
Gav

Reputation: 393

Creating a table in google sheets for use with table formulas

I have a google sheet with formulas that go by the cell in the previous row (e.g. =IF(D12=FALSE,E11,E11+1). When I insert a new row, the formula doesn't copy into the new row.

I was looking to create a table like you could in excel so that you could write formulas like =[@[header]] and when you inserted a new row, this carried into the new row.

Does anyone know how you can either create an array formula for the two below formulas, or how to format as table so you can define the =[@[header]] functions?

Two formulas that could be written as array:

For my sheet this one is inserted in cell E2: =IF(D2=FALSE,E1,E1+1)

This formula is in F2: =IF(E1=E2,"",E2) - both continue for the entire column.

Upvotes: 1

Views: 515

Answers (2)

Mateo Randwolf
Mateo Randwolf

Reputation: 2920

Solution

For adding headers to your formulas using also ARRAYFORMULA just wrap the whole thing with {}. Note that in the second formula I have written E3 instead of E2 as E2 would have the title of its formula.

These are the formulas to be used in your specific scenario:

For E2 :

={"title 1";ARRAYFORMULA(IF(D2=FALSE, E1, E1+1))}

and for F2 :

={"title 2"; ARRAYFORMULA(IF(E1=E3, "", E3))}

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

Upvotes: 0

player0
player0

Reputation: 1

for first use:

=ARRAYFORMULA(IF(D2:D1000=FALSE, E1:E999, E1:E999+1))

and the second:

=ARRAYFORMULA(IF(E1:E999=E2:E1000,,E2:E1000))

if you want it with header then:

={"header title"; ARRAYFORMULA(IF(E1:E999=E2:E1000,,E2:E1000))}

Upvotes: 0

Related Questions