Replace part of a formula

user_16711051 0
Viewed 31

I have a formula that I run within a Google sheet. The formula extracts data from a database and it is segmented by the name of the business. The formula is:

=query(importrange("https://docs.google.com/spreadsheets/d/1j-2QkMRB8VG5YumBh0nZ_fbwoQW87xxJ4wz2NZqd8s4/edit#gid=1504008710","Log for Print!A:E"), "select * where Col3 = 'ABC Shop - Sydney'")

To extract the data for Music Shop - London, the user has to change the formula each time.

Is it possible to build a button that refers to a cell where the operator enters the name of the business and this replaces the text?

enter image description here

Then, the macro opens this data in a new sheet.

google-apps-script google-sheets
1 Answers

Try

=query(importrange("https://docs.google.com/spreadsheets/d/1j-2QkMRB8VG5YumBh0nZ_fbwoQW87xxJ4wz2NZqd8s4/edit#gid=1504008710","Log for Print!A:E"), "select * where Col3 = '"&B1&"' ")

instead of button, you can build a drop-down list in B1 with

=unique(query(importrange("https://docs.google.com/spreadsheets/d/1j-2QkMRB8VG5YumBh0nZ_fbwoQW87xxJ4wz2NZqd8s4/edit#gid=1504008710","Log for Print!A:E"), "select Col3 "))

https://docs.google.com/spreadsheets/d/1ycrVMCUtbtlR_VCWwg3fy2LL4gNKO-m1yCF1RlgHdeA/edit?usp=sharing

user_150193800
Related