r/googlesheets

Conditional Formatting help

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!

https://preview.redd.it/hu76izqk4ekh1.png?width=2879&format=png&auto=webp&s=57db2ec32cbe9c06623f7776c8c2bec35dc804df

u/TreacleOutrageous960 — 1 day ago

▲ 6 r/googlesheets

Weighted Average with Drop-Down List

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!!!

u/mads18_ — 1 day ago

conditional formatting for a dropdown?

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

u/No_Illustrator3532 — 1 day ago

dependent drop down is not working, can't figure it out!

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!

u/truffledumpkins — 1 day ago

▲ 5 r/googlesheets+1 crossposts

Help Making a Custom Character Sheet

Hello all! My playgroup plays a fairly heavily homebrewed version of 5.5e involving each player having 2 classes, but not gestalt rules. We've been getting by using a Google Sheet and a lot of working around certain problems, but I wanted to see if we could get any help finding a program to make a custom sheet in. The goal is to have it be as "automated" as possible, calculating simple things like ability scores, hit points, to hit bonuses, etc. The main issue with the current sheet is that there's no way to reset it beyond manually changing all of the numbers back.

The ideal end product would be something similar to D&D Beyond, but I also know that isn't a realistic goal right now. If there would be any suggestions, they would be greatly appreciated! And for anyone interested in seeing our Google Sheet as it is now (mostly working, still has kinks in it), here's a link for it: https://docs.google.com/spreadsheets/d/1XtquqZNIPLv8H4mJFZ0eC0WMYrzCXd3vNRR88ER-24w/edit?usp=sharing

u/GodzillaEating — 2 days ago

https://preview.redd.it/k0jrya7wf4kh1.png?width=682&format=png&auto=webp&s=824c34dbe99138c3931dddb275fc46b4eda3976b

How do I automatically sort by date?

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

u/WeBombus — 2 days ago

Referencing the contents of a cell relative to another cell.

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?

u/Wooden_Secretary_101 — 3 days ago

Custom Rule If C2:C1000 says Tier 2 or Tier 3 AND D2:1000=False then A2:A1000 highlighted Red

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.

https://preview.redd.it/wis3lhjrh0kh1.png?width=730&format=png&auto=webp&s=d58675f778485a8e3a7d8b91100d3ca50d2bb7cb

u/iamdetermination — 2 days ago

▲ 0 r/googlesheets

I need to take avgs of grades for students with matching IDs

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!

https://preview.redd.it/kqpa3mwpb0kh1.png?width=601&format=png&auto=webp&s=7970c40bf09947de5cb4d20341ed54d6aeb359ef

u/NatTwenty20 — 3 days ago

▲ 0 r/googlesheets

Profit Percentage Formula

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!

https://preview.redd.it/st1mhbuck0kh1.png?width=1881&format=png&auto=webp&s=7b6dc5cf6e84a82308b5b87a203b8e1b4f848c6d

u/Tricky_Piglet_2763 — 3 days ago

▲ 0 r/googlesheets

Creating a search bar

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)

u/Responsible-Set-8338 — 3 days ago

https://preview.redd.it/gppauwhrhvjh1.png?width=952&format=png&auto=webp&s=985c0331f3b36550997243acb43e891656476927

Roster: name lookup and allocations

I asked a question for help setting up a formula to lookup the date, shift and state allocation and retuning the name of the team member covering the shift.
This was the suggested formula.

=LET(

inWeek1, ISNUMBER(XMATCH(E2, $C$11:$I$11)),

dayStates, IF(inWeek1, XLOOKUP(E2, $C$11:$I$11, $C$13:$I$21), XLOOKUP(E2, $C$26:$I$26, $C$28:$I$36)),

employees, OFFSET($B$13, ROW(dayStates)-13, 0, 9, 1),

MAP(E3:E6, LAMBDA(state, XLOOKUP(state, dayStates, employees, ) )))

This covered the first 2 weeks, how do i now amend it. so it covers all future dates, runs through to end of year and into the next year.

Thanks heaps

u/Bradisssa — 4 days ago

https://preview.redd.it/ulz9opoowxjh1.png?width=308&format=png&auto=webp&s=143a619bd3a2e3e1ade592521c72e8fc628f0ed3

Highlighting duplicates on different sheets is giving false positive results

Hey everyone, I'm trying to highlight a word in one column in a sheet that is also in a column in another sheet. For example I'm trying to highlight the word "Apple" in column B in sheet "common" as it's also in in column B in sheet "Test".

I looked around and I read that I should put a custom formula in conditional formatting with the most common formula seemingly being:

=COUNTIF(INDIRECT("Test!B:B"),B2)>0

However it's giving me false positives in the column B of sheet common. It's highlighting a vast amounts of cells that aren't in column B of sheet test.

Does anyone have an idea what I could do amend that?

Thanks in advance for any help!

u/-MrJester — 3 days ago

https://preview.redd.it/cb6ond1fayjh1.png?width=478&format=png&auto=webp&s=f9e070ca37e7da5c4d2fd33f63d62243f0e36189

SUM amounts by month for budget spreadsheet

Hi! I'm working on a budget spreadsheet and I'm trying to get the Payment Plans row to auto populate from a Payment Plans table so that the amount in the Budget is only equal to the amount due during a set month. Pictures to explain better:

These are the amounts and due dates of upcoming payments. One is in August and two are in September.

This is the row in the Budget table where I want to add up only the amounts due during the set month. The amount in August should be -13.19 and the amount in September should be -26.38.

https://preview.redd.it/2hg1hl7qayjh1.png?width=813&format=png&auto=webp&s=c20debe626f00d86cb09e1189b4597127bef63a4

How do I set up the function to draw totals only from the corresponding months?

Thanks!

u/Working_Analyst_5136 — 3 days ago

▲ 6 r/googlesheets

How can I make a dropdown that changes based on the first dropdown's answer?

I don't have much experience with Google Sheets, but I'm trying to make a book tracking spreadsheet. I've already written down a list of the genres and subgenres specific to the first genre.

How can I make it so you are forced to choose a genre in the first column, and afterwards, the subgenre list has options pertaining to that specific genre only? For instance, if my book is fantasy, I can click that as the genre, and when I move to the subgenre cell, the dropdown only shows specific fantasy subgenres.

Also I feel it is important to note that not every genre has a subgenre, so that's also made it kind of confusing while I was working on it.

Here's what I got so far: https://docs.google.com/spreadsheets/d/1HtYIohmmwO1a-Q_MeoiFgHu83EAFCtErky7gputHVJ0/edit?usp=sharing

edit: i've figured it out! thanks for all the help!!

u/HotIceCreamCone14 — 4 days ago

Auto Sorting Alphabetically from Data in another tab + Sorting manually entered data in same row

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!

u/Will_532 — 3 days ago

Using Conditional Formatting with Alternating Colors

Trying to get my columns (AA31:AB59) to alternate colors based on a text value in cell AT2, with each text value corresponding to a different color scheme:

If you see "Word1" in cell AT2, then use green alternating color scheme in AA31:AB59

If you see "Word2" in cell AT2, then use blue alternating color scheme in AA31:AB59

If you see "Word3" in cell AT2, then use red alternating color scheme in AA31:AB59

I have =$AT$2="Word1" to get it all one color but I'm not sure how to achieve alternating colors for the column

EDIT: PART 2
I also would like the non-zero numbers in AA31:AB59 to be bolded if/when they are input, but it seems a second conditional formatting rule cancels out the color-based formatting

u/TSL_FIFA — 4 days ago

https://preview.redd.it/ian3c8agprjh1.png?width=328&format=png&auto=webp&s=fd80bb8a1334f059be74532eb68caebf9bca4984

Return data that is not in each both table

https://preview.redd.it/3a3sqoylnrjh1.png?width=321&format=png&auto=webp&s=47e4a9644c500fc8d394ae0650d5d7d41cb97440

Compare the 2 tables in Image 1 and return data so its like Image 2

What I looking to do is for it to compare A+B to C+D

First remove all Names that match

Second compares all IDs that match (excluding ones without IDs)

Finally do the same with C+D

Im currently using "Sort(FILTER(A2:B,ISNA(MATCH(B2:B,D2:D,0))))" for A+B to remove the names in both, just Cant seem to figure out how to do the same with the IDs and have it ignore the blanks

im Fine with having to do this over multiple columns to do this if needed

link to sheet, it's the (Filter Data) Sheet

https://docs.google.com/spreadsheets/d/1_OWO7HrK9vwbfp8QRY_s8R59ABKdlYr_hCH2Q-PNLQo/edit?usp=sharing

u/madbomb122 — 4 days ago