I don't have programming background, but as a novice, experiment with formulas on my own.
I have two separate live sheets;
On the 1st sheet (sheet A) tab F, when I write Y
into any cell in Column O, say O6
, I need the data in the next column(Q) same row (6): P6
, to be transferred (dynamically -thus assumed the formula as indirect- updated for each row) into sheetB column D. Since these are live separate sheets I know that I need to use importrange
but dont know how to write the formula.
=IMPORTRANGE("SheetA";indirect("F!P")& IF(P6:P:275, "Y"))
Update #1: Did some reading and research, and found out query
function might work better, thus have come up with the formulas below. However, while the first currently gives me formula parse error
, the second gives value!
warning.
=QUERY(IMPORTRANGE("SheetA","F!O:P"), “Select #(Col2) where Y N(Col1) contains ‘Y’ “)
=QUERY(IMPORTRANGE("SheetA","F!O:P"), "Select * Where Col2=<>"")")
Update #2: here are the sheets, should anyone want to take a look; Demo 1 Demo 2
I need the data on Demo 1, P7
, to automatically be added to Demo 2, D11
. Moreover, if possible, I need this formula cover all the rows (+to be added) on Demo 1 sheet, while all, say 25 occurrences out of 350, are added under each on Demo 2, without moving the whole list from Demo 1.
My starting point was to tag the Y
s with an if
formula that created the number in P7
. That's why on Demo 1, N
creates FALSE
, whereas Y
1010101010
. However, if there is any other option/formula, all fine by me. Otherwise, manual calculation takes too much time.
"
are wrong."
should not be used inside of the second argument oF QUERY, i.e. instead of"Y"
you should use'Y'
, second tip, instead ofCol2=<>""
useCol2=<>''
orCol2 is not null
)5
this is wrong but I don't know what are you trying to do with that.