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.
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)