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!

48 hour Tri-Fold Flier for Cell Phone & Electronic Repair Store $50USD PayPal

I am in need of a Tri-Fold Flier. We will provide all the text content but to make the contest easier you can use filler text and then after we pick a winner we will discuss the actual content at that time. The name of the business is eRepairz. We repair Cell Phone, Laptops, Computers, Game Consoles, Tablets, and many other electronics and devices people use everyday. We also do service calls for residential customers and businesses. We build custom computers, servers and gaming systems….

48 hour Tri-Fold Flier for Cell Phone & Electronic Repair Store $ 50USD PayPal

Google Sheets populate X cells with formulas based on single cell containing value X?

Google sheets dynamically populate cells based on single cell value. I don’t know what the term would be called and a bunch of searching hasn’t resulted in any lead on what it would be called.

What I would like to do is populate X number of rows with the formulas based upon a cell that has the number value of X.

An example would be an amortization sheet for a loan. If you have a cell that contains the term years 15 that’s 180 periods (15*12). The formula is the same for each column and dragging them down 180 rows would give me the same results it would auto-populate the cells. If I then changed the term cell value to 30 years loan I would have to drag it another 180 rows for a total of 360.

Instead, I’m trying to figure out how to have the sheet auto-fill the values based on the term cell number. It would then auto-populate the rows based on that value.

What is this term called or how could I go about doing this? Do I need to create my own macro in the sheets script?

Reducing run time to extract cell values from multiband rasters?

I have the following function and code snippet to extract cell values for multiple years, format it, and save to a list. Each raster has 365 bands — one for each day. A separate operation is performed on each flattened list pr_flt which contains daily cell values of XY for 1991-2015. The cursor contains XY coordinates for each cell. The following code works but it takes hours to finish due to the huge amount of data and possible use of lists instead of multidimensional arrays. I am wondering if there are any suggestions to reduce the runtime of the code, if possible.

import arcpy  def row_to_values(row):     values = []     for col in row:         if isinstance(col, unicode) and col != u'f':             # split and convert all entries to float             values += (float(v) for v in col.split(','))         else:             values.append(col)     return values   with arcpy.da.SearchCursor(fc, fields) as cursor:     for idx, row in enumerate(cursor):         pt_annual_lists = []         pt_loc = str(row[0]) +  str(" ") + str(row[1])         for y in xrange(1991,2016,1):             pt_year_list = []             result = arcpy.GetCellValue_management(in_raster = "D:/temp/ras" + str(y) + ".tiff", location_point = pt_loc, band_index="")             cell_value = result.getOutput(0)             pt_year_list.append(cell_value)             cell_value = [s.replace('\n', ',') for s in pt_year_list]                         pt_annual_lists.append(row_to_values(cell_value))           # flatten list of lists         pr_str = [val for sublist in pt_annual_lists for val in sublist]         pr_flt = [float(i) for i in pr_str] 

Best way to insert a clear button in a datagrid cell

I have a datagrid (or, more general, a data structure) in which the user, with a proper button, can clear data by only one column. Since that requirements force me to implement different moments to clear data for each single line, is it correct to replicate a “clear” icon in every column’s cells? (Image below). Is there a way to achieve the same behaviour with a smarter UI decision?

Thanks.

Data Structure

In each cell of $n$x$n$ is a number so that in every $3$x$3$ subtable the sum of numbers is negative and the sum of all numbers is positive.

In each unit cell of $ n\times n$ table we have a number so that in every $ 3\times 3$ subtable the sum of numbers is negative and the sum of all numbers is positive. For which $ n\geq 4$ we can have such an arangment?


Clearly we don’t have such an arangemnet if $ 3\mid n$ . Now suppose $ 3\nmid n$ . Then if $ n=7$ (or $ 3$ or $ 10$ or $ 13$ ) we see that configuration

\begin{array} {|r|r|r|r|r|r|r|} \hline 7 & -1 & -1& 7&-1&-1&7 \ \hline -1 & -1 & -1& -1&-1&-1&-1 \ \hline -1 & -1 & -1& -1&-1&-1&-1 \ \hline 7 & -1 & -1& 7&-1&-1&7 \ \hline -1 & -1 & -1& -1&-1&-1&-1 \ \hline -1 & -1 & -1& -1&-1&-1&-1 \ \hline 7 & -1 & -1& 7&-1&-1&7 \ \hline \end{array}

Works. So I tried to generalised this for arbitrary $ 3n+1$ , instead of $ 7$ we put positive $ a$ and instead of $ -1$ we put negative $ b$ . So I have to prove that there are such $ a,b$ that satisfies $ a+8b<0$ and $ $ (n+1)^2a+(8n^2+4n)b>0$ $ for arbitrary $ n$ , but I fail to do that. Any idea how to solve this.