google sheets – How to link to source cell from filter output

I have a simple query that outputs columns A and B of a project dataset, when the value in column A is not empty:

QUERY(Projects!A2:B, “SELECT A,B WHERE A<>””)

This creates an easy to scan highlight of the important project data (in my case, project name and next action item). However, the project details are in another tab, so if I want to examine the project, I have to copy the name, go to the tab, and search for the project.

It would be super nice if the output of the QUERY could automatically hyperlink to the source data. Or maybe there is a hotkey I can press to jump to the source. Does this feature exist?

formulas – Google sheet – highlihghting cell if in the same row other cell not empty

I’m trying to make a worksheet where I log shift hours.

I made columns with dates and to each date a row of hours

I want the date text to be green if in the same row a cell is not empty..

The code I used here apply only for column B and I don’t know how to do it so the other columns applied as well =NOT(ISBLANK(B4:B43))

If possible.. I would also prefer to do it so I will not have to add any letters like - inside the cell but only color the cells

enter image description here

cyberpunk 2020 – Datfortress’s programs are always visible in their cells, or triggered only when Netrunner pass by their cell?

In all the samples of Netmap, we can see data fortress with walls, gates, CPUs, memories, and also programs in a determined cell.

Now I wonder, when the netrunner enters in the subgrid and starts to attack the fortress, does he see the program in a far cell (if visibility is not blocked by an obstacle and withing 20 spaces)?
Or the map that the netrunner sees, doesn’t show the location of programs and will be the referee to decide when a program appear in that determined cell, probably triggered by the netrunner or other programs passing by that cell?

Also, not all programs have “endurance”, and they should be deactivated at the end of their action at the end of the turn, so how is possible that Datafortress has all its programs always activated?

So this makes me think that programs are “preassigned” to a cell, but they are like in a sleeping mode, and only after triggering they appear doing their action.
Can somebody explain how you did in your netrunner mini-games?

What’s the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets?

What’s the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets?

Online searchers tell me it’s apple (command) + T but this just opens up another tab in Chrome. I guess they mean Excel.

Anyone know the shortcut for this is for Sheets?

GOOGLE SHEETS: Referencing a cell from another sheet using a value in another cell

I’m trying to find a way to reference a cell from another sheet using a value in another cell from the “home sheet.” I’ve tried to use INDIRECT but I’m not quite sure how to use it when the cell I’m trying to reference is from another sheet.

For example:

Let’s say that I have two sheets, Sheet1 and Sheet2.

Cell B30 of Sheet2 contains data I want to be referenced in cell A2 of Sheet1. In Sheet1, the cell A1 contains the number 30.

So in cell A2 of Sheet1, I want to reference cell Sheet2!B<the number that is in Sheet1!A1>.

Does anyone know how I can go about this, if I can at all? As mentioned I tried using INDIRECT but not exactly sure if this works if the cell referenced is from another sheet.

excel – How to use csv writerow while appending xlrd cell value Python

I wrote the following function that copies data from numerous excel files and copies them to csv. I am having trouble appending the “refresh_date_value” from a single cell in excel and iterating with writerow to include that date value with every row in my csv output. Currently it takes the “refresh_date_value” and splits every character into seperate columns. For example, the output files puts every character in its own column (2, 0, 2, 0, /, 0, 6….). I’d just like to have the entire date in one column for each row. The “refresh_date_value” is a date time object so I didn’t know how to join in writerow without turning it into a list. I’ve found some examples on Stack similar, but have been unable to get the desired results. Thank you in advance.


def append_all(input_directory):
    for file in os.listdir(input_directory):
        if bool(, file)) == True:
            in_fpath = os.path.join(input_directory, file)
            out_fpath = os.path.join(input_directory, 'history.csv')
            wrkbk = xlrd.open_workbook(in_fpath)
            wrksht = wrkbk.sheet_by_name('importer')
            refresh_date_float = wrksht.cell_value(1,4)
            refresh_date_value = xlrd.xldate_as_datetime(refresh_date_float, wrkbk.datemode).strftime('%Y/%m/%d %H:%M')
            if os.path.isfile(out_fpath) == False:
                with open(out_fpath, 'w', newline='') as csvfile:
                    wr = csv.writer(csvfile)
                    for rownum in range(3, wrksht.nrows):   
                        wr.writerow(wrksht.row_values(rownum) + list(refresh_date_value))
                        # Start append data
                with open(out_fpath, 'a', newline='') as csvfile:
                    wr = csv.writer(csvfile)
                    for rownum in range(4, wrksht.nrows):
                        wr.writerow(wrksht.row_values(rownum)  + list(refresh_date_value))
    print('process complete')


keyboard – Shortcut for Selecting Current Cell

Is there any shortcut or keyboard hot-key which selects the current cell? I am basically looking for the exact effect as clicking on cell bracket but achieved using keyboard instead of mouse.

I frequently comment and de-comment cells while testing my code using Alt-/ but it is very frustrating to click on cell bracket every time I want to do that.

Please let me know if this is a trivial question and I will delete it.

google sheets – How to Jump to Column Based off of Cell Value

My goal is to have a drop-down list with a “JUMP” button. The words in this drop-down list will be a collection of about 85 column titles. Since there are so many columns, I want the user to select the column title, then click the button and automatically jump to that exact column.

I’ve done this before with jumping to rows, but am having issues figuring it out for columns – I could not find anything like this.

Here’s what I have so far:

MATCH function set up in Cell AP1, searching for the dropdown value location within the top row. This basically returns the Column # that I want the jump button to move to.

Then, I have my script:

function jump2() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getSheetByName("ClassicGames"); // change to sheet containing dates
      var r = s.getRange("AP1").getValue(); //change A1 to cell containing =match formula

I thought maybe this combination would just take me to whatever number the MATCH function finds. For instance, if r = 35, the user will move to the 35th column.

I thought maybe s.column would work, but it appears I am just making up functions at this point.

Any ideas?

Google Sheets conditional formatting based on cell above it

I have a column of blank cells. If I type something into a cell, I want the cell immediately below that one to turn red. Any ideas for conditional formatting help?

google sheets – Creating a Recursive Formula in a single cell?

I have searched Google and stack exchange extensively and cannot find a solution that fits my needs.

I have a recursive formula in a google spreadsheet that can be represented as

F(0) = 0

F(1) = 5000

F(n) = F(n-1) + ((n-1) * 5000)

I can effectively calculate this with a long two column table with n values in column A and the recursive formula in column B which references the previous cell when needing to access F(n-1).

This approach is clunky, takes up space, and requires a separate table for every different calculation. In my sheet I need to calculate this formula for different values of F(0) and it requires a separate table for each one.

What I want is a simple single cell with a formula that references another cell for the n value and calculates the result recursively all in the background, instead of taking up a whole set of space in a table somewhere in my sheet for it to do the recursion through multiple cells.

My spreadsheet to make it clearer:

Is this possible?