1

I’m trying to figure out the formula in google sheets - if the cell is “yes”, then copy the adjacent cell to another sheet. Can anyone help?

1
  • 1
    I don't know if Google Sheet has similar functions to Microsoft Excel. If so, you can use VBA to complete it. Simply record a macro. Nov 16, 2020 at 9:26

1 Answer 1

2

You have:

yes  1
no   2
no   3
yes  4
no   5

You want (on another sheet):

1
4

Easiest solution but with some manual operation: Add a filter to the main sheet Filter for yes answers Copy and paste to the new sheet

Scripting: If the data changes a lot and you need to do this often you can record a macro using Apps Script (Google is your friend). This is the equivalent to VBA in Excel.

Using formulas: "If true then copy" as a FORMULA in a cell implies that the cell with the formula must change the value in another cell. This is counter to the way a spreadsheet works. Rather imagine you are entering a formula into the cell that you want to copy to. This formula can look to any cell and reference its value (not copy). A basic solution:

=IF(A1 = "yes", B1, IF(A2 = "yes", B2, IF(A3 = "yes", A3, "")))

There are of course several problems with this approach. It requires long nested IF statements (or IFS function) and you have to type the IF condition and result for every row in your dataset. Worst still the next cell has no idea how far down the previous cell "looked". To solve this you need to use a match or lookup search function that can return the n'th yes result:

n  value
1  *return 1st yes result
2  *return 2nd yes result

Now if you are familiar with VLOOKUP you'll know it will only return 1st result. To get this right though you need some ARRAY FORMULA and follow this master piece https://infoinspired.com/google-docs/spreadsheet/find-nth-occurrence-in-google-sheets/

There is luckily also the poor man's n'th lookup - if you have a static or single lookup value like "yes". Add a column to the left of your original data. You'll want to count the number of "yes" occurrences to become a lookup index for your second sheet:

Count  Condition    Value
1      yes          1
1      no           2
1      no           3
2      yes          4
2      no           5

This formula in A2 will look like this:

=IF(B2 = "yes", IF(A1 = "Count", 1, A1 + 1), IF(A1 = "Count", 0, A1))

In your other sheet put (in A1) and copy down:

=VLOOKUP(ROW(), Sheet1!A1:C1000, 3, FALSE)
3
  • 2
    Thank you!!! 🙏🙏
    – Katiekate
    Nov 20, 2020 at 16:37
  • 1
    Dear @Katiekate, kindly approve the answer using the check mark so others know it has been solved. Thanks! Nov 21, 2020 at 5:58
  • 1
    @Mobus,, nice explanation you get +10 ☺ Even a simple Vlookup can do this ,,, look for yes and get the adjacent cell value from the column. Nov 21, 2020 at 6:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .