CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Whirlagon
Whirlagon

Reputation: 71

Google Sheets - How to conditionally copy an entire column?

I'm trying to write a formula to copy an entire column from one sheet to another, depending on a condition. The current formula I have is:

=ARRAYFORMULA(IFS(Settings!B7="Combined MPG", 'Raw Data'!B:B, Settings!B7="City MPG", 'Raw Data'!C:C, Settings!B7="Highway MPG", 'Raw Data'!D:D))

This formula only fills the cell that it is in, instead of its column. Does anybody know what I did wrong?

As an example, if the condition Settings!B7="City MPG" is true, I would like for the column that this formula is in to be filled with the data from column D in the sheet Raw Data.

Upvotes: 0

Views: 916

Answers (2)

Whirlagon
Whirlagon

Reputation: 71

I found a way to make it work. It's slightly counter intuitive, but when I added a new condition that can never be true using a parallel array formula at the beginning of the IFS formula, it made the entire formula work. Here is the updated formula:

=ARRAYFORMULA(IFS(Settings!A:A="thiswillneverexist","",Settings!B7="Combined MPG", 'Raw Data'!B:B, Settings!B7="City MPG", 'Raw Data'!C:C, Settings!B7="Highway MPG", 'Raw Data'!D:D))

Upvotes: 0

Gav
Gav

Reputation: 393

You could give the QUERY function ago:

=IF(Settings!B7="Combined MPG",QUERY('Raw Data'!D:D),"")

This works fine for me so hopefully will sort your problem.

Upvotes: 1

Related Questions