ARRAYFORMULA + QUERY + IMPORTRANGE – Final cell reference in IMPORTRANGE is acting like it’s absolute when I need it to be relative

I know I’m chaining a lot together here, but it’s SO close to functional. Here is my formula with the doc address altered for privacy.

=ARRAYFORMULA(IF(ISBLANK($ AE$ 3:$ AE),(query(importrange(“https://docs.google.com/spreadsheets/d/XXXDkgregmq16N91iBOjKcMH3B74w9PoEExrHRWGdaP4/edit#gid=1834499049″,”Brite Fills Lot Truncation!A1000:H”),”select Col2, Col6 Where Col1= ‘”&(’14 oz Bottles’!$ G3:$ G)&”‘”))))

In short, I want this formula to auto-fill into a sheet that collects responses for a Google Form. It cross references another sheet and returns data based on the value of the G column in my current sheet. The only issue is what is in bold above. This function ONLY returns the value that matches $ G$ 3 in my current sheet, acting as if it’s an absolute cell reference. I want it to be relative so that it fills each line based on whatever value is in the G cell on that row.

I would be thankful for any insight!

It’s been a while…

Good Afternoon,

It has been many years since I have been on these lovely DigitalPoint Forums and I have spent the last hour having a look round and catching up on old threads and new ones.

It is nice to see that the Forum has lot's of new members; and I am glad to be back as an active member.

So, hello from the United Kingdom.

MacBook thinks it’s overheating when it isn’t?

I have a MacBook (oldish late 2012 retina 13″) that seems to exhibit classic overheating symptoms, except that it isn’t hot.

Under load it tends to shut down after a while, can be turned back on after a few minutes, and can be “fixed” by using iStat Menus to force the fans to always run. (Of course iStat Menus couldn’t help during OS upgrade, so to be able to finish that, I had to put the MacBook in the fridge — that worked.)

However, it’s barely lukewarm to the touch, and according to iStat Menus, none of its temperature readings are high. But when CPU Die temp goes above 70C (CPU Proximity ~55C), it shuts down. I think that should only happen somewhere above 100C, correct? Without the iStat Menus override the fans wouldn’t even start running yet when it shuts down.

I’ve already tried SMC reset, PRAM/NVRAM reset, and fully reinstalling macOS; none of these helped. Any further ideas for how I could fix it?

How to Extract raw script from tx messages, which it’s output script starts with 0xA9?

I’m working on extracting output addresses from tx messages in BITCOIN packets.

currently, i extract address from output scripts which starts with 0x76(OP_DUP) like this :

76 a9 14 6d 1d 74 58 95 6e 80 cd b4 c3 3f 1e d5 8e 91 c4 92 1a 85 d0 88 

but i don’t have any idea about some output scripts which start with 0xA9(OP_HASH160).

As an example, i don’t know in below example, what is the raw script, which is used to generate output address?

a9 14 16 52 33 02 f2 ee d0 e0 aa 6a 4c 1d 0a 41 39 6f c2 6f 53 6e 87 

How i can extract output address from output scripts which start with -xA9?

How to stop Apple Watch to display music controls when it’s playing on iPhone?

For e. g., when driving a car I am absolutely not interested to have those controls taking all the Apple Watch screen since I’m way more interested in having access to clock face and all the notifications things instead. I easily can switch music with car’s own controls if needed. Even If I press the button to get to main screen notification upper area is taken with that “iPhone is playing music” icon.

Moreover, taken how Apple cares about driving safety it’s becoming even more irrational: a driver obviously would have to control music play on Apple Watch with other hand than the watch are worn on, thus it implies changing focus to a small display, clicking on it with fingers of another hand while trying to hold the wheel — nonsense.

Does not using CAS when it’s availible encourage phishing?

My (U.S., R1) academic institution uses CAS to authenticate almost all our web services. However, they don’t use CAS for our GitHub Enterprise instance; rather they use LDAP, so the login page says “GitHub Enterprise”, and there is nothing to indicate it’s an official page of our institution except that the URL is under our .edu domain. Beyond the fact that this bypasses our Duo 2FA deployment, I’m concerned that presenting users with a login page that expects their CAS password but isn’t the normal CAS login page encourages phishing attacks by diluting the message of only putting your password into clearly official, institutionally-affiliated websites. Is this valid, and is it worth raising with our campus IT department? (I’m a graduate student.)

Can I inter Bulgaria first by my single entry schengen visa , stay in Bulgaria 3 days then go to Italy , it’s my first time to inter Europe

I have schengen visa , single entry, I’m applying the visa From Italian consulate in Palestine I want to visit Italy and Bulgaria, I should go to Italy first Then go to Bulgaria ( notice that my visa is one entry and Bulgaria is not a schengen zone ) Or I can go to Bulgaria first finish my work then go to Italy ? Thanks for helping me enter image description here

How should a binary tree and it’s node class be implemented in Java?

This may seem trivial, but I wanted some input. In implementing a binary tree in Java, should the node class be a separate class file independent of the BinaryTreeclass, or should it be a default class in the same class file as the BinaryTree class?


First Example: Node is in separate class file

BinaryTree.java

public class BinaryTree {     ... } 

BinaryTreeNode.java

public class BinaryTreeNode {     ... } 

Second Example: Node class is default class in same class file

BinaryTree.java

public class BinaryTree {     .... } class BinaryTreeNode {     ... } 

I almost never see the use case for putting more than one class inside of the same class file, but this might be the first time I see it being useful. Does this make sense, or would this be considered sloppy code?