r/googlesheets

https://preview.redd.it/anjavj913ouh1.png?width=863&format=png&auto=webp&s=3b6d29a40f4bfb6ada52ca426d90cb191bdd1843

I've been working on a livedex for a browser-based pokemon game I've been playing, and they've recently added a new paradox form for ambipom. How do I add a cell specifically for one column without fucking up the nearby ones? I've had to add cells before after forgetting a pokemon from my list and it created a lot of awkward spacing for the entire doc that I had to adjust. It was extremely annoying... I don't wanna do that again LOL

Thank you!

I'm attempting a formula and it's not going well, and I don't know how else to word it when searching so I come to Reddit. I need something along the lines of

D11: If B11="A" then show S8 value, if B11="B" then show S9, If B11="C" then show S10...etc

There are six options in Cell B11 as a drop down, options are A, B, C...F. S8:S13 are all simple formulas themselves (=number*cell value). I also need this in in the cell above but with B10 as the drop down and R8:R13 as the returned values.

I know it should work, it worked with =IF(SEARCH(B11,"A"),S8) but I can't get it to be multiple choices. I tried OR and IFS but I'm lost and have been trying for well over an hour. I'm also not the most experienced with Google sheets I've just made spreadsheets for budgeting, meal prep and travel planning.

Sorry for any confusion but any help would be much appreciated!

https://preview.redd.it/jqy81jv54kuh1.png?width=678&format=png&auto=webp&s=48dc84d1a903bcf2125eb0888fb5c328bc36f713

This is purely an example, I have no code and have been doing things entirely manually. What I would like to do is follow a certain sequence by inputting a text and the next cell automatically guessing what comes next. I attempted with IFS in another sheet while referencing the original one so I could still input text in the ones being modified (if that even makes sense) and it didn't work,,, I only know the basic of the basic, so any help would be immensely appreciated. (sequence is w/g/m > x > w/g/m > x > w/g/m > x > house)

I have an excel sheet with a column of links that I want to copy and then paste onto Google Docs. Is there a way for me to do that?

Good morning! EDIT: MY 3rd TOCOL is the problem.

So, i have a grid that has slots that may or may not be filled with names.

I'm performing TOCOL on this list to put them all in a column (list) with no gaps, which it is.

However, in another spot on the sheet I turned all of those names into values based on where they are. so in that copy. each cell looks something like =if(D5<>"", "2", "")

So when I run a TOCOL on the copy. it doesnt remove the "blanks" because there is a formula in those cells.

Okay, no big deal. I figure. if I run a 3rd TOCOL on the 2nd TOCOL that didnt remove the "blanks". It would remove the actual blanks present in the 2nd TOCOL. but to my surprise it isnt.

For reference.

1st TOCOL, which works is =TOCOL(C1:E28, 1), gives me all the names in a list
2nd TOCOL, which doesnt "work" is =TOCOL(C50:E77, 1), it gives me all the cells because each cell contains that value formula.
the 3rd TOCOL looks like =TOCOL(P2:P84, 1), which is just supposed to perform TOCOL on the 2nd TOCOL.

Hi!

I want to create a sheet that will allow me to track the percentage of my sick days (i can't be over a certain number so it's important for me to know how many days i have left) from the total days, so e.g. when i have 10 days of work and miss one it would be 10 percent. what i don't get is:

\- how do i add a button that will automatically add 1 to the number of sick days so i can just click it when i do have a sick day

\- is there a formula that automatically adds 1 to the number of total days when it's a weekday, so from mon-fri e.g. when it's been 20 days of school so far, every day it will add 1, so 21 on monday, 22 on tuesday and so on. that will allow me to keep track of the relative percentage (as i believe it's called? idk, i'm not a maths expert lmao) rather than just the absolute percentage of the whole academic year.

I'm completely new to sheets but i've been playing around for a few hours, so far no luck.

if anybody has any tipps, formulas or websites with good formulas (so far i've only been able to find pages like "30 basic formulas everyone whould know", no extended list or anything. i've looked here in the subreddit for similar requests or even parts of it but haven't been successfull so far.

thank you in advance!

side note: i'm aware that AI could give me an answer in like 2 secs, but i'm opposed to using it out of environmental and moral concerns.

Hi all,

I have a trigger running to center the cells of our spreadsheets, however it only centers the first sheet and not all. Is there a way to center all of the sheets instead of just the first one?

See what I have below. Thank you for the help!

function centerAllText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange(); // Gets the range containing data
  range.setHorizontalAlignment("center");
  range.setVerticalAlignment("middle"); 
}function centerAllText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange(); // Gets the range containing data
  range.setHorizontalAlignment("center");
  range.setVerticalAlignment("middle"); 
}

Hey all. I am brand new to this and teaching myself. I want to preset a column to see if a set of other cells have a number in them. If not, I want it to act as if they are zero. If they do have any numbers, I want it to round the average of the numbers. So far the formula I’ve come up with is

=IF(ISBLANK(D4:M4),"0", ROUND(AVERAGE(D4:M4),0)

I know this isn’t right and doesn’t work. I just don’t know what I’m messing up. Any and all advice would be greatly appreciated!

Edit: I think u/whonose2238 fixed it! Thank you all for your help!!!

“There is a second issue: ISBLANK(D4:M4) is checking a range, which can produce an array of TRUE/FALSE values rather than simply asking whether the whole range is blank.

If what you mean is “if all cells D4:M4 are blank, return 0; otherwise average the populated cells and round to a whole number”, I'd use:

=IF(COUNTA(D4:M4)=0, 0, ROUND(AVERAGE(D4:M4),0)) “

Hi! I was able to get this script to work for one of the tabs in my work's google sheets, but I cannot figure out how to get it to be able to auto-trigger and uncheck checkboxes on two different specific tabs, with two different specific ranges.

function timedresetboxes() {


var spreadsheet = SpreadsheetApp.getActive();


spreadsheet.getRange('B6:B29').uncheck();


}

Currently, I am using this^ through the Google Apps Script and it runs and correctly unchecks the boxes on the tab I need.

However, I guess it does not specify the tab name in the sheet I am using it for. I am not versed at all in coding, and found the above script to use from a different reddit thread.

I need to uncheck boxes at midnight daily on the following tabs with the following ranges:

tab name: アマ
range: B6:B29

tab name: DAILY TASK 🌴
ranges: F5:F20 and K5:K:20

I trust reddit people to know what to do, it is probably an easy fix for someone who knows what they are doing! Thank you!

I can insert a drop-down calendar in a cell via Data Validation and it works. Now I have a sheet that is table formatted, when I try Data Validation the only option that is available is for Drop-down text. How do I insert a drop down calendar in a Table formatted Google Sheet? Thanks for your help.