Tl;Dr: This is possible by using Google Apps Script and/or the Google Sheets API
There isn't a "built-in" function like IMPORTRANGE that updates the formulas of dependent spreadsheets based on a "master" spreadsheet.
You could develop or borrow an algorithm (step by step instructions) and implement that algorithm using your preferred programming "...
d/m/Y is an ambiguous date formatting.
It's very likely that this occurs because the spreadsheet regional setting used in the spreadsheet doesn't support d/m/Y as a date formatting. Please note that the screenshot included in the question shows column J values having a "day" value greater than 12, with exception of 11/01/2019 have the a white cell ...
I am the OP. I tried switch and it almost worked but I couldn't quite string it out right with that function. I searched through a function list however and found the IFS function and that seems to be working wonderfully. Thank you for trying to help pjmg. Sorry I didn't have more information and then quickly worked it out myself. I was stressed with trying ...
This is simply due to the number being negative, remember formatting is funny in google sheets and the student could format a positive number to look like a negative number or vice versa. A simple test is to do =A1>0 or =A1<0
If you want it all in one cell you need to use Query:
=query(importrange("https://docs.google.com/spreadsheets/d/1q87c_0rFdt8FcWtIzD0KVW3k1ZpuvhfeWb1281MEs","DEMOS!A2:C1000"),"select count(Col1) where Col2 = 'Rep1' and Col1 >= Date '2019-11-9' and Col1 <= Date '2019-11-15' label count(Col1) ''")
You can use some If statements or maybe use the function =switch().
Here's a way to do it :
The formula :
If the ...
It is not clear if you want to pull data from another tab of the document you are working on or you want to pull data from another tab of the "url of my document" and don't want to type the whole thing again.
Pulling info from the same document but different tab using query would be as easy as replacing importrange("url of my document"; "INPUT!A1:F28") ...
Use an auxiliary range
Google Sheets data validation could use a custom formula to accept or reject a value but not to set a list of valid values that are shown in a dropdown, but it could use values from a range.
There are several ways to do this. i.e.
Transpose the values
Add the above ...
"" is an empty text value, COUNTA counts it as a value.
ISBLANK returns TRUE if the reference is blank (meaning it hasn't any value at all). It's similar to nullin some programming languages but not exactly the same.
Google Sheets, as well as other ...
How it works
The core logic is to create a transposed copy of the values of A1:A5 for each element of A1:A5 then remove the first element for the first copy , the second element of the second copy, and so on, then join A1:A5 ...
Test for the length of the cell contents; it is is greater than zero, then B2, else ""
The specific sheet reference is untested, but taken from your example.
=if(len('Lee''s sheet'!$AP2)>0,'Lee''s sheet'!B2,"")