Formula to Reference an Outside Sheet If Specific Text In Cell Range Is Present It Will Compare Text in Two Ranges and Populate Text

I need a formula to reference an external workbook for to query a cell range for a certain entry to exist, and if that specific text is present, it will cross reference the a cell from a certain column in the same row and check if it is present in a column of the source sheet – if it does, it can populate with “Present”, and if it does not, populates with “Needs Added”.

In these example workbooks, I would like “Example Spreadsheet” Column B to house this formula. The Formulas will reference “Example Spreadsheet 2” cell range $A$2:$A for “Larry’s”, where “Larry’s” is present, it will compare cell range $D$2:$D in “Example Spreadsheet 2” to $C$2:$C in “Example Spreadsheet”. If present in both, it will populate with “Present” and if the store number is missing from $D$2:$D, it will populate with “Needs Added”.

The Links to the Example Spreadsheets are:

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.

In the RECORDS I have a list of names, in alphabetical order, and a column for BATCHES(E9).

What I wish to have as an output is to AUTOMATICALLY bring/distribute the batches from RESPONSES!B4:B to the respective attendees in RECORD TAB (E10:E).

NOTE: The names are already available in the Google Form(Checkbox type), so the list of names in RECORD TAB are just the same as in the Google Form.

But the problem is, there are responses in C4:C that has multiple names in a single cell, and I don’t know what to do anymore. hehe. I have already tried the INDEX and MATCH formulas based on how I understood it as a total beginner in Google Sheet, but it only works on the first name.

I hope that someone could help me achieve the output that I wanted to achieve.

Thank you so much and may God bless everyone! 🙂

Here’s the screenshot of my sample project:

Here's the screenshot of my sample project:

prove that a sequence whose range is relatively compact admits a convergent partial sequence

I need help with this demo please.
prove that a sequence whose range is relatively compact admits a convergent partial sequence.
the truth is that I have looked for information but I can not

Average range only if another range is blank

I am wondering how to write a formula that will do the following.

Average A1 and B1, if A1 is not blank.
If it is blank, average E1 and B1.
If it is also blank, average K1 and B1.

Is there any way to do this? Thank you so much in advance!

Google sheets range of sheets?

I have multiple sheets in my Google Spreadsheet. Let’s suppose that they are named “1”, “2”, … “n”. I would like to sum the contents of the B4 cells in all of these sheets. Is it possible to specify a range of sheets, or do I need to meticulously include a reference to each sheet?

IP range quote in UK


I’m interested in* /24, /23 /22 IP ranges + server + 1Gbps, monthly payment

Please, let me know your monthly price for
/22 + s… | Read the rest of

networking – BitDefender firewall: allow single IP instead of a range

I want to allow a single IP within my LAN to be able to connect to my guest server which runs on a host with NAT networking in virtualbox. I configured port forwarding in virtualbox to pass the incoming traffic from the host to the guest vm. Bitdefender firewall is running on the host, no firewall on the guest. I also can’t use a bridged network since my setup doesn’t allow me to have a firewall on the guest vm so all the filtering needs to be done on the host.

Creating rules for IP ranges works, but for some reason it doesn’t work for a single IP.
|IP input|Affected IP’s(I think)|Status|
|–|—|— |
Table in words: If I create a new rule with IP:, only the 192.168.100.x devices are able to connect. If I put 200 instead of 100 only the 192.168.200.x devices can connect. But for some reason this does not work for a single IP address.
I tried entering, nothing.
I tried, nothing.
The port always stays the same in my tests.

I really can’t figure out what I’m doing wrong.

intervals – Computing possibles values (range) for an (polynomial) expression that takes values from two 2-simplex

Let $(p,q) in Delta_2$ and $(k,m) in Delta_2$, where
$Delta_2$ is the 2-simplex, that is,
$0leq pleq 1$, $0leq qleq 1$, and $0leq 1-p-qleq 1$.

Consider the expression

2 (p - q) + 3 k^2 (-1 + 2 p + q) + 
 k (1 + 6 (-1 + m) p + (3 - 6 m) q) - 
 m (1 + 3 p - 6 q + 3 m (-1 + p + 2 q))

I want to find the values that the expression above can take, given the
conditions on p, q, m, and k.

I tried an approach using Interval

expression(p_, q_, m_, k_) := 
 2 (p - q) + 3 k^2 (-1 + 2 p + q) + 
  k (1 + 6 (-1 + m) p + (3 - 6 m) q) - 
  m (1 + 3 p - 6 q + 3 m (-1 + p + 2 q))

and observing the values that it takes

substitute($p_, $k_) := 
 N((2 (p - q) + 3 k^2 (-1 + 2 p + q) + 
      k (1 + 6 (-1 + m) p + (3 - 6 m) q) - 
      m (1 + 3 p - 6 q + 3 m (-1 + p + 2 q)) /. {p -> $p, 
      k -> $k}) /. {q -> Interval({0, 1 - $p}), 
    m -> Interval({0, 1 - $k})})


Manipulate(substitute(p, q), {p, 0, 1, 1/10}, {q, 0, 1, 1/10})

However, using this values in the original expression, return lower values:

Table(expression(0, i, j, 0), {i, 0, 1, .1}, {j, 0, 1, .1}) // 
  Flatten // MinMax

autofocus – Limit focus range of Panasonic Lumix DMC-FZ330

My Panasonic Lumix DMC-FZ330 has a button on side of the lens that only focuses on close objects for macro photography, or focuses on more distant objects for non-macro photography.

Is it possible to further refine the range that the camera searches for objects to focus on? For example can I set the camera to search for objects to focus on that are between 20m and 100m?

How to run a google sheets query against a dynamic range

I have a spreadsheet that displays data internet consumption by users that looks like this

userid    feb1    feb2    feb3     .    feb29 
u1        100     34      23       .     4
u2        0       24      21             62
u3        300     25      5              1
u4        50      5       6      
un        23      52      3        .     42

I already wrote a formula that lists the daily consumption of each user like so:

date        daily consumption
feb1        14,971     
feb2        6,898       
feb3        10,666     

feb29        10,543     

from the daily consumption I wrote a formula that gives me the highest amount consumed by a user on a daily basis so it looks like something like this

date        daily consumption    highest consumption
feb1        14,971               7,523
feb2        6,898                3,422
feb3        10,666               5,411

feb29        10,543              6,234

(this is what the query is where March is the sheet that contains the raw data

query(transpose(QUERY(March!I2:AT,”Select “&textjoin(“,”,true,ARRAYFORMULA(“Max(“& REGEXEXTRACT(address(1,column(March!I1:AT),4,true,”March”),”March!(.*)d”) &”)”)) &””,0)),”select Col2″)

What I’m trying to do now is find out on a daily basis the ID of the highest consumer like so:

date        daily consumption    highest consumption    highest consumer id
feb1        14,971               7,523                  4779221
feb2        6,898                3,422                  1047223  
feb3        10,666               5,411                  7505020

feb29        10,543              6,234                  2994922

I have two ways of getting the highest consumer Id manually:

VLOOKUP method


=vlookup(D2  ,{March!$I:$I,March!$B:$B},2,false)
=vlookup(D3  ,{March!$J:$J,March!$B:$B},2,false)
=vlookup(D4  ,{March!$K:$K,March!$B:$B},2,false)

query method


=query({March!$I:$I,March!$B:$B},"select Col2 where Col1 contains "&trunc(D2,5))
=query({March!$J:$J,March!$B:$B},"select Col2 where Col1 contains "&trunc(D3,5))
=query({March!$K:$K,March!$B:$B},"select Col2 where Col1 contains "&trunc(D4,5))

However I’m struggling with writing a formula that makes either work in a single line. What I’m trying to do is something along these lines:

for vlookup option:

arrayformula(vlookup(D2:D3,indirect(“March!$”& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,”March”),”March!(.)d”) &”:$” & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,”March”),”March!(.)d”)), indirect(“March!$B:$B”),2,false))

for query option:

arrayformula(query({indirect(“March!$”& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,”March”),”March!(.)d”) &”:$” & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,”March”),”March!(.)d”)), indirect(“March!$B:$B”)},”select *”))

How do I make this work?