Copying data from one sheet to another based on value in a cell

Question

I have a Google sheet with multiple sheets. The Ambassador users sheet has a list of multiple users (ID, Email, Coupon, and three more irrelevant columns). Each new user is updated to the sheet via Zapier. I can have three users with coupon 1234, four with ABCD and two with XYZ. I then create a unique sheet for each type of coupon (also via Zapier) and want to update each sheet only with the users that have the correct coupon for that sheet. The coupon is also listed in cell J1 on each sheet. I need the update to happen automatically without pressing any buttons.

I do not know how to use the functions on Google sheets (I understand it's different from VBA), and I though using a function would be the best solution. I tried using the IF function in conjunction with the INDEX function and it worked, however, it requires me to copy the function into each row, and thus reduces the automation option.

=if('Ambassador users'!$C3=$J$1, index('Ambassador users'!A3:G3),"")

Then I tried to use the IMPORTRANGE function, and this worked, but not in conjunction with the IF

=if('Ambassador users'!$C2=$J$1, importrange("1QHGSCR_pVepNlMtjFshvGnI-vSPzgqi3g9jz98","'Ambassador users'!A2:G11"),"")

This gave me all the rows in the Ambassador users sheet.

I think I'm doing something wrong with the IF statement in the initial range I'm setting is wrong. I also tried to set a range in the IF, but that totally didn't work.

player0 · Accepted Answer · 2019-07-22 09:50:56Z

1

try like this with ARRAYFORMULA:

=ARRAYFORMULA(IF('Ambassador users'!C3:C=J1, 'Ambassador users'!A3:G, ))

or perhaps FILTER:

=FILTER('Ambassador users'!A3:G, 'Ambassador users'!C3:C=J1)

answered Jul 22, 2019 at 9:50

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Buddy Over a year ago

Both worked amazingly. The FILTER was better, because it didn't bring empty rows, only the rows that had the correct content!!. The ARRAYFORMULA brought the right results but left empty rows. Thank you so much for your help!!

Collectives™ on Stack Overflow

Copying data from one sheet to another based on value in a cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related