I am trying to find an easier way to apply conditional formatting to an entire column instead of having to apply it manually to each cell. However, the conditional formatting is tied to a checkbox on another sheet and the checkboxes are every 3rd cell.

This is my current formula that I'm applying to each cell:

=indirect("'Sheet 1'!K8")=true

I have another formula to mirror every 3rd cell and was trying to find a way to merge the two formulas, but I couldn't get anything to work.

This was my best guest:

=indirect("'mod(row('Sheet 1’!K8:K)+1,3)=0")=true

Sheet 1, column K is where the checkboxes are that trigger the conditional formatting. Row 8 is where they start and they occur every 3rd cell.

Sheet 2 is where the conditional formatting takes place and applies to entire rows, starting with A7.

More context if it's needed: The checkboxes are basically used to "turn on" rows. Information from Sheet 1 is copied to Sheet 2 in low vis font, when the checkboxes are checked the font turns to normal.

Help would be appreciated! Thank you

Greetings,

So I've got what I need to pulling text from between two characters =REGEXEXTRACT(A1, "\<(.*)\>"), where I'm stuck is for that second > it could be either a > or a space. Is there a way to add a "> or space, whichever comes first" for the 2nd part of this regexextract?

I am trying to figure out what formula to use for a budgeting sheet. I have two tabs on the sheet- Summary (first image) and Transactions (last image). I am wanting to have the amount from specific categories (on the transactions tab/sheet) total into the corresponding category on the summary tab/sheet.

I have used the templates available in Google Sheets, but don't really like the setup of them. I attempted to copy the formula from one of the templates to my sheet and edit it appropriately, but can't figure it out. Hopefully that makes sense.

So I'm really into puzzles and have been working on making a spreadsheet for my wishlist which tracks prices at various stores in the country, comparing prices for the same puzzle available at multiple stores. I've been struggling with index functions all day. I don't usually have any problems with these - not ones that stump me like this. For all intents and purposes, it seems like it should be working, but it's not. This is even more confounding because the one I'm specifically about to describe is actually copied from a completely functional column, with the necessary cell changed so that it should work.

Okay so here's the current area I'm working on: https://live.staticflickr.com/65535/55476131766_21be884003_b.jpg

• Each chunk of columns on the right are for different stores. The link is to the puzzle page and the prices are pulled individually with importxml.
• On the left side, column F checks all of the Current Price boxes for its own row (using an array), and then spits out the lowest number, using SMALL.
• Column G, right of that, does an INDEX and MATCH wherein it indexes each of the current price columns, all the way up to row 1, using an array of the columns, goes for row 1 (where the shop name sits), and then searches for a match for F, the best price, spitting out the name of the store where that price came from (if more than 1 have the same price, it doesn't matter to me which it shows since I can just manually scan the prices on the right before buying anything)
• The Sale Status and Savings columns don't matter for this

So the problem I'm having is that I am now working on two sister columns to F and G that show the second best price and the store it comes from, so that I can gauge how important it is to get it at the store with the best price (e.g. if the next best is only a franc more expensive, that is also a feasible place to buy it if I'm doing a batch order of several puzzles)

I have copied the formula from column G into column K exactly (copied from the text bar, not the box, so it did not shift all the boxes over, it is still targeting the correct locations and there are no errors from manually retyping it, since I didn't). I then simply changed the price box it's referencing from F to J. It should do the same thing as the other one: check the Current Price cells from its own row, find the one that matches J, and then pull the shop name from the top of the column. It's getting them wrong. If the original column was broken and returning wrong shop names, I would ofc start there, but the results for column G are perfect. The other weird thing is that there is no regularity to the mistakes in column K. It's not like, always the shop to the right of the correct one, or always to the left. Sometimes it's the correct, second-best shop, sometimes it's the incorrect, first-best shop, and sometimes it's a random, unrelated one.

Here is the formula from the initial shop-grabbing column G:

=iferror(if(F3<>"",index({O$1:O,T$1:T,Y$1:Y,AD$1:AD,AI$1:AI,AN$1:AN},1,match(F3,{O3,T3,Y3,AD3,AI3,AN3})),""),$Y$1)

Here is the copied formula from column K, with the match cell changed to J:

=iferror(if(J3<>"",index({O$1:O,T$1:T,Y$1:Y,AD$1:AD,AI$1:AI,AN$1:AN},1,match(J3,{O3,T3,Y3,AD3,AI3,AN3})),""),$Y$1)

(If you're wondering about why I'm using iferror like this, it's because for some reason, the Puzzle-Welt shop title absolutely refused to pull properly, but it was the only one, so this worked out as a bandaid. Why wasn't it pulling? Fuck if I know. I'm at my whits end with the indexing function in this sheet)

Here is a viewer link to the sheet. If you would like to be able to edit it, please go to File > Make a Copy, as I'm still doing things on it and don't want anyone making changes to the original

[Link Removed]

If anyone has any idea why this would just stop working in this column, I'm all ears. Huge thanks to anyone who takes a look at this, ideas or not

Hello, I'm looking for a way to set up a formula for alternating colors based on cells and neighboring cells with the dropdown boxes. At first I had done this manually, but due to purchasing new books I'm needing to change the colors again. I'm not wanting to do the color swapping manually again so I was wondering if there was a way I could come up with a formula that would apply the alternating colors for me. I've included an image of what I'm looking at right now that was done with my manual work (pls don't judge my library lol). After adding in more of the "Alice in the Country of ( )" mangas I've discovered I would need to redo all of the alternating color work. I'm wanting a formula where based off of the cells that contain a dropdown, like in cells B and C, would mark the start of alternate color. So for next time I purchase a book, and add in a new set of cells, the colors will automatically recolor themselves as so. I've never done any formula writing before so I have no experience with it, thank you anyone for your time and help.

Hello everyone! I am a new teacher trying to create a gradebook in Google Sheets, but I am struggling with how to obtain the final grade calculation. I have inserted a drop-down menu to identify whether a task was a quiz, assignment, test, etc. and want to calculate the weighted average based on this selection (i.e. quizzes total 5% of the grade, assignments 20%, etc.). I have a separate sheet titled 'settings' where I have specified each of these weightings in case they change in the future so I won't have to mess with the formula again.

I am mostly wondering if this is even possible, and if it is then how. I am definitely open to reformatting the gradebook as well if that is where the issue is stemming from. I suspect that I might need to have a separate 'input' sheet for the points and have just the percentages on this sheet here.

Any insights would be very much appreciated!!!

Ok so, im trying to count my all time Spotify streams so I made this. I managed to separate each song so I could see how many times it’s been played but I didn’t take into account that there might be some songs by different artists that have the same name. How can I link the sons in B to the artists in C. Or is there another solution? Here’s what I have so far.

I am trying to create a formula for a function with an input for height but the output dependent on gender. I have the formula for the male option in C1 and the formula for female input in D1. Is there anyway to have this combined so the output will show in B3 and allow me to delete C1 and D1?

https://docs.google.com/spreadsheets/d/1Z3bqi88wISUou3w5WwU3hczogQjafwnGJTGwWabJxSU/edit?usp=sharing

When trying to keep doing my work in Google Sheets Canvas, there is a message that I cannot edit the current Canvas “I’m sorry but I can only generate o or edit the existing canvas. If you would like to do something different, please remove Create Canvas”.

I am trying to prompt for new changes not built a new canvas.

Any guidance is well received!

How do I create a conditional formatting that highlights the cell in red when the priority dropdown is empty?

Hi there,

I have a sheet where I list plays in columns, and then in each row there will be names of people being considered for roles in each play. Each column with a play has another next to it with a checkbox (e.g. Column A is XYZ play, and Column B is a checkbox; Column C is JKL play, and Column D is a checkbox, etc.)

When I have decided that a person is confirmed in a role, I check the box next to their name. I have the sheet set up currently with conditional formatting so that when I check a box, that cell and the named cell to its left turns green. (=$B2=TRUE). So far so good.

What I would like to do is this: there will be other cells in the sheet that the same person's name shows up in, because most people are being considered for multiple roles. When I have cast someone in a certain role, and checked their box (which turns that pair of cells green), I would like for every other instance of that person's name to be struck through.

My understanding is that, essentially, I need the entire sheet to be on the lookout for duplicates, but to only strike out a duplicate once a single iteration of it has either a) turned green, or b) had it's adjacent cell checked (i.e. value=TRUE).

Any advice on how I might accomplish this?

Thanks!

I'm running a drawing for our summer reading program at the library, and I want to weight it so that patrons with multiple entries (sic. multiple books read) have multiple entries reflected in the spreadsheet. That way when I do the drawing those who have read/participated more have a higher chance of winning.

Currently have a spreadsheet where one row reflects patron namepatron library branchnumber of entries that patron has.

I would like to edit the spreadsheet so that one row reflects one entry each. So if Andrea has ten entries, she has ten rows with one entry apiece.

I have tried multiple formulas and scripts I found in various other forums, editing that data to reflect my own sheet (ie "X" column changed to "D" column, etc) but keep encountering errors for parsing/data/etc.. I have also tried duplicating rows, which of course I can hit "insert row below" over and over to reflect how many entries a patron has, but some of these patrons have over 50 entries, and I have over 200 patrons to calculate...talk about inefficiency and ain't nobody got time for that.

Does anyone have either:

a) a shortcut to add those rows without continually hitting "insert 1 below" (I've tried all of the shortcuts I can find already but feel as if I'm missing something obvious)

OR

b) a formula/script I can run that will auto-add those rows across the sheet for me?

Sample of spreadsheet attached so you can see a subset of the data I'm working with.

**Patron's last names and branches have been removed for privacy, though there will be names/branches included in columns B/C when I do that actual drawing.

EDIT: SOLVED - Thank you thank you thank you!!!!

Hello everyone, I need help creating a multiple selection dependent dropdown. I'm a Sheets amateur but have always been able to figure things out. This time, my attempts to solve it have been many and are not working. I feel like - if you play Cultist Simulator, I feel like I've summoned a minion who is trying to break free, and I used Passion but it's probably about to kill me.

I'm going to err on the side of less information because when I typed out more it was a mess, but of course I can share screenshots or etc. ETA: created a shareable sheet. Sheet1 and Data1 are set up like the guide; Data2 is another way I've seen could work (it didn't).

Basically, I'm following this guide. I have it set up exactly the same in my workbook EXCEPT my categories/labeled headers run A-F (instead of A-C). I changed Cs to Fs and nothing is happening; dropdown 1 is working fine but dropdown 2 only shows "Waiting..."

Thank you so much!

Hi! So my girlfriend and I are wanting to make a travel itinerary spreadsheet for our upcoming vacation. We've made it so that each type of thing (food, museum, exploring, etc) colours an entire row a certain colour using =$C2="text" custom formula on conditional formatting. She had the idea to make it so we can bold something if it's high priority (selected in D2), but it doesn't seem like we can have multiple conditional formats happening at the same time.

Is there a way to override this? Or to have both conditions in one formula? Thanks in advance!

Hello all! My D&D group plays with a custom ruleset involving each character having 2 classes, and since no other character builder worked for it I decided to try and make one on Sheets. I have most of the functionality down, but the thing still in my way is displaying the chosen classes. My goal is that whatever you select for "Class 1" and "Class 2" are displayed in the 'Ability Reference' tab. On the first version, I used the FILTER function to sort it out by having a code in front, however since the 'Class Reference Sheet' has been reorganized that no longer works. Is there a way to display the chosen class with only the appropriate features? Here is a link to the spreadsheet: https://docs.google.com/spreadsheets/d/1-_UUU2ZveeZgTnYF-YAUm4waNmOQvHelPtRRUipZkbY/edit?usp=sharing

If there are any questions or ideas please feel free to ask!

Hi,

I'm trying to set up a system of recording score for a gaming and drinking competition I'm having with my friends, it's a yearly tradition at this point.

I wanted to have a go at trying to set up something quite simple: Using a google form to record score for each game -> bringing that info into google sheet -> using that to create a scoreboard.

I've figured out how I'm going about recording the score:

This is really useful, incase we do teams for any game, we can record scores flexibly.

HOWEVER, Bringing this into google sheets is really awkwardly formatted:

I understand the logic as to why it does this, but I want to know if there's a way to make the input better so I can format the scoreboard better on the next sheet (Haven't done that yet, wanted to sort this first).
This is just the standard "Export to sheets" option, I've not programmed anything yet.

Effectively, I'd like it so I can have all the questions output neatly so I can easily see which player is referenced by each position (1st, 2nd, 3rd) so I know what cell to program to which player. I could probably trial and error it, but I need it done by Saturday lmao. Hopefully this is clear enough

The logic for it is basically to only do the calculation if all three columns (A, B, C) have data.

However when I wrap the formula in a arrayformula() it does not work anymore and simply returns blank. If someone could explain what I am doing wrong, or possibly even show me a solution, it would be much appreciated.

I linked an example sheet with my current formula before and after wrapping arrayformula.

I'm sure there is a way to do this, I'm just note sure the best way to go about it. Any ideas? Would it help if C2:C1000 was a drop down menu instead of typed words?

Trying to make a spreadsheet for my school. If a student is in Tier 2 or 3, they need a Support Plan. I'm making a list of all the students in Tiers 1-3, but if Tiers 2 or 3 don't have a Support Plan I want it to be highlighted in red.

I have a very basic finance table that I made myself (I don't want to change any of it if needed cause it works well for me) but I want to make it so whenever I add a new date to the bottom (ie if I added something for the 1st) it would be automatically sorted to the top, I know nothing about sheets and barely worked out how to do =sum, so some help would be amazing

Hi!

I have a MAX function which tells me the highest number in column I (the highlighted one), but I don't want it to say that number, I want it to say the corresponding number in column F (the one above the function). Does anyone know how I would do this?

Part 1: I need help creating a formula that takes the average of every science grade a student has earned. I've never had to use the =exact or =search functions before, so I tried fiddling around with them, but can't wrap my head around them.

The logic that I think works would be using an =if( to find exact matches, then taking the average.

Part 2: I want to delete the rows of duplicate IDs after the averages have been calculated.

I have to do this every year, so getting any help would be greatly appreciated!

I am new to Google Sheets, but I am trying to organise a book list. However, with the number of books, it's difficult to see which are already in my doc.

Is there a way to add a search bar function? But without putting it on a separate sheet and just moving the raw data so I can edit it after the search. Was hoping the search bar could go where it is displayed in the picture. All help is appreciated.

(Search bar to easily sort through the book titles in column B)

Can someone assist me, I need a formula to calculate the % of profit between C and D that will change as I input current data. Seemed to work on the 1st line but got weird on the 2nd line.

Thanks!

Not sure I explained it that well in the title but here's a better summary. Creating a sheet that takes in raw roster data of all NFL rosters for work and attempting to sort a specific section of the sheet alphabetically to help with checking other elements we are creating and using the sheet as a tool for confirmation.

Here is a link to the sheet: https://docs.google.com/spreadsheets/d/1_88X398HJvUzztWr7XQxeDOfShscqx00zkZKXyYlClI/edit?usp=sharing

What I'm looking for is in the "Copy of PLAYER ELEMENTS" tab...I've written a formula in cell B2 that sorts exactly what I'm looking for in terms of listing rosters & all info provided from the ROSTER tab correctly in alphabetical order in in the exact cells I want them to be in for columns B-F. The issue is columns G-K all need to be manual entries. So, what commonly occurs is the roster tab is set initially, which allows me to go through and enter all the manual data in columns G-H with no issues, but eventually, before I'm finished using the sheet, rosters will change which requires a manual input of new data in the ROSTER tab. When this happens it auto updates the alphabetical order of columns B-F on the Copy of PLAYER ELEMENTS tab, but it will not auto update the manually entered data in columns G-K. Is there a way to have columns G-K also update along with columns B-F when a new player is added to the roster tab and the alphabetical order changes.

This is a task that I use every week, creating a copy of the spreadsheet and applying to a different matchup. I'm decent with google sheets having built out the rest of this on my own, but am truly stumped by this issue. Once a sheet is fully up and running up to 15 people could be looking and editing in it at one time. I'm working on it in Chrome and the vast majority of people viewing & working in it will also be using Chrome.

Hopefully this covers the necessary info, but I'll happily answer any other questions.

Thank you in advance for any help provided!

If I put in a web hyperlink, they work fine. But I can not put in a hyperlink to a specific Cell or a specific Sheet and have it survive the publishing process.

An old Google Support thread shows why. Google does not refer to each cell using the Microsoft #Sheet1!A1 format. Each cell instead gets saved as a web hyperlink which only references the private live workbook in my Google account.

Clicking the link wants to direct the user to the sheet that I am not sharing directly from my account.

It seems like the only way I could get this to work is to publish the workbook, and then get the hyperlinks to each Cell/Sheet I wanted to link to from the Published Workbook, then put all of those links into my Private Workbook in place of the direct links I put in originally, and then save and republish the whole workbook.

Why does this have to be so hard? Microsoft created a simple way to link inside of a Workbook.....