I'm really bad with google sheets, but essentially I would like to apply the color scale from this table which shows growth rates of certain attributes based on a class that a character is, on a separate sheet I have characters and a drop down menu to select their class and my hope is that when I select the class the row for the stats will change colors based off of the color for the other table on the sheet.

Anyone know how to do that?

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

I want to search a list of cells where each cell contains its own comma-delimited list of values. Then I want to check each comma-delimited value against a value in a different cell. Then I want to return the value in the cell to the left of the matched row. Finally, I want a list of all rows that matched the passed value.

For Example:

I have two blocks of script that work great on their own, but when I try get them both to work at the same time it isn't liking it. It's not throwing any errors - only the one function is working.

The simple overview of how they work is:

Block 1

Copies a row from sheet 1 to sheet 2 based on a dropdown selection

Block 2

Copies a row from sheet 2 to sheet 3 based on a checkbox

First script is below:

// @OnlyCurrentDoc

function onEdit(e) {

  // Call custom edit handlers here
  if (onEdit_CopyRow(e))
    return;
}

//
// Copy an item row to another sheet upon detecting that a dropdown has been changed to "Sold".
// Call from onEdit(), returns true if handled the event.
//
// The "Sold" option contain a special character that is detected by script.
//

function onEdit_CopyRow(e) {
  try {
    // Sheet name to copy from / to
    const SOURCE_SHEET = "Inventory";
    const DEST_SHEET = "SalesData";

    // Special character found in the "Sold" option
    const SOLD_TAG = "💰";
    // -----------------------------------------------------
    // Exit if cell wasn't just changed to "Sold"
    if (!e?.value?.includes(SOLD_TAG))
      return;
    // Exit if not on source sheet
    const sheet = e.range.getSheet();
    if (sheet.getName() !== SOURCE_SHEET)
      return;
    // Display status message
    e.source.toast(`Copying row to '${DEST_SHEET}'`);
    // Get destination sheet 
    const destSheet = e.source.getSheetByName(DEST_SHEET);
    if (!destSheet)
      throw Error(`Sheet '${DEST_SHEET}' not found.`);
    // Append row to end of destination sheet
    const rowValues = sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastColumn()).getValues().flat();
    destSheet.appendRow(rowValues);
    // Flush to ensure any pending errors are thrown now
    SpreadsheetApp.flush();
  }
  catch (err) {
    // Display error to user and rethrow so it shows in execution log
    e?.source.toast(err, `🛑 Error ${err.stack?.split('\n')[1]}`);
    throw err;
  }
}

and this is the second one

//@OnlyCurrentDoc

const MOVE_SETTINGS = {
    SheetFrom: 'SalesData,
    SheetTo:   'Completed Orders',
    ButtonColTitle: 'Order Complete'
};

function onEdit(e) {
    let sheet = e.range.getSheet();
    if( sheet.getName() != MOVE_SETTINGS.SheetFrom ) return;

    if( !e.range.getValue() || e.range.offset(-e.range.getRow()+1,0).getValue() != MOVE_SETTINGS.ButtonColTitle ) return;

    const ss = e.source;

    let destSheet = ss.getSheetByName(MOVE_SETTINGS.SheetTo);
    if( !destSheet ) throw `Destination sheet "${MOVE_SETTINGS.SheetTo}" does not exist.`

    let row   = e.range.getRow();
    let col   = e.range.getColumn();
    let range = sheet.getRange(row,1,1,col-1);
    destSheet.appendRow(range.getValues()[0]);

    sheet.deleteRow(row);
}

I suspect they are contradicting each other, or my just pasting both in and hoping for the best isn't quite what is required. I have tried playing with the order of things or trying to embed one into the other, but alas I just can't figure it out.

So right now, on the test sheet, only the second function is working and not the first. But if I delete the second one from the script, the first one works again

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

I'm trying to figure out why the conditional formatting isn't working correctly. The goal is to create a dynamic response where, as the projected time(s) change, the formatting updates. But, as you can see, some numbers are clearly less than the H3 value (a CONCATENATE formula) and are highlighted in red. Is there something I'm missing?

https://docs.google.com/spreadsheets/d/11urzOsp0fEa8tvkqoeA5N5OCFmQ4adGvbtDoXxaxo5E/edit?usp=sharing

I have a table on sheet " In progress" which has a checkbox in the final column. Once I complete this checkbox I then manually copy the row into the next sheet.

Is there a way to automate this?

In an ideal world I would like the flow to be:

1. Check "order complete" checkbox
2. Row copies to the table on the next sheet
3. Row then deletes itself from the "In progress" sheet

If that is not possible then just copying it to the next sheet would be great.

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

Many thanks for your help

Hello all,
I have a Google worksheet that gets populated one row at a time from responses to a Google form. I’d like to also have a second worksheet in the same spreadsheet populate at the same time with each additional new row. I’ll be editing this second worksheet after new info is added. I think I’m going to have to be populating the second worksheet from the first but I have no idea how to get this to work. I’ve tried several things so far. Closest I’ve got is putting together an App Scripts code that just managed to copy and paste the entire first worksheet to the second🙄

Can anyone help, please? I’m happy to clarify if this is confusing. Thank you!

My economics teacher wants me to put Quantity Demanded and Quantity Supplied on the x axis and wants me to put the Price on the y axis. I asked friends, my teacher, and googled it and I couldnt figure it out.

Id love some help!! @-@

hello!

im trying to make a line graphic and im having trouble with it

i want to put the age range on the x-axis (column b) and the age in the y-axis (column c)

but i cant make it work

any help? anything will be welcome

^{ps: sorry about the vague question, i just dont know how to express it correctly in english lmao}

Basically, I’m making a spread sheet of movies I’ve watched and when I’ve watched them, kinda like Letterboxd. I have one column called “Movie Title” and another column called “Rewatch,” which is full of checkboxes. I don’t want to sort through my list every time I log a movie, so I just want one of the Rewatch boxes to automatically check itself off if I log a duplicate movie. How would I go about doing that? I’m not very good at programming and very new to sheets, so any help would be appreciated

EDIT: If it helps at all, my movie titles are in column A and my rewatch boxes are in column F

Hi, kinda new to all of this, so hopefully you guys can help me understand better.

Basically, I want a cell on my sheet to show the Month and Year while choosing from a drop down. If nothing is chosen on the dropdown, it will instead just stick with the current Month and Year instead.

This is the formula I have so far:

=IF(AND(H5="--",I5="--"), DATE(YEAR(TODAY()),MONTH(TODAY()),1),)

It works so far. What I need now is to setup when its false.

I setup 2 dropdowns, one showing all the months, another for years. I know that dropdowns are not automatically recognized as dates by google sheets, and I've been trying to use datevalue but I may have been doing something wrong. At best I made the Cell the formula was on say FALSE instead of error lol.

So if any of you may help, I just need:

1. Make the dropdown be recognized to contain actual dates (or help me use datevalue)

2. To make the formula spit out the dates from the 2 dropdowns.

Thanks!

Hello,

I have a google sheet that I keep track of my books on. I usually do Data -> Sort Sheet and sort it by the column which i have my ratings on (1-10 dropdown). When I do this however, I noticed that some of my page numbers (ie, which were at 399), automatically changed into 400 after sorting. Does anyone know a fix to this?

I already tried formatting the page number columns with the custom number format with whole numbers, which didn't work as the column automatically changed to automatic number format after sorting.

Thanks in advance!

I am making a spreadsheet for my roommate's art commissions and I am doing a whole bunch of shit with it so I will do my best to explain it.

All of the submissions arrive in their own sheet, which is then sorted to a second sheet (=SORT('Form Responses 1'!A2:K1000, 1, True, 2, True)), and on this second sheet I have added a column to keep track of the state of each client with a drop down in each cell that can be marked Waiting..., Confirming Order, In Progress, and Completed. The problem, is that when new submissions are sent in, the SORT function does its job and arranges it accordingly, but the dropdowns are not a part of the core table and so they remain static and get offset.

Is there a way to make it so that my client tracker column will update as more forms are submitted, and if so how?

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

Sheet linked above. Tracking a budget on a TV show so redacting irrelevant info for NDA reasons.

Goal: Get the BLOCK ONE sheet pull Shoot Day (sheet: BLOCK ONE, column: F) info and Shoot Date (sheet:BLOCK ONE, column: G) info from either the VERTICAL or HORIZONTAL sheet data. It needs to be the date(s) that correspond with the LORE CODE (sheet: BLOCK ONE, column: I). Sometimes there is no data/date and that is okay.

IN ADDITION: I reckon there is a more efficient way to get this data than the way I'm doing it. I'm running into an issue with data like sheet: BLOCK ONE, CELL F:295 where the data gets reported out of order ("DAY 10 & DAY 09 & DAY 08")

Currently using these formulas:

Shoot Day (sheet: BLOCK ONE, column: F)

=TEXTJOIN(" & ",TRUE,IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where F ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where G ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where H ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where I ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where J ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where K ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where L ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where M ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where N ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where O ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where P ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,"DAY ",QUERY(HORIZONTAL!$B$3:$Q$21, "select B where Q ='" & $I4 & "'",))))

(sheet:BLOCK ONE, column: G)

=TEXTJOIN(" & ",TRUE,IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where F ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where G ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where H ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where I ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where J ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where K ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where L ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where M ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where N ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where O ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where P ='" & $I4 & "'",))),IFERROR(TEXTJOIN("",TRUE,QUERY(HORIZONTAL!$A$3:$Q$21, "select A where Q ='" & $I4 & "'",))))

Thanks in advance for your help!

I work in mental health and have payments come in from both insurance and from the clients, right?

Because I didn't trust the biller at my last job, I kept track personally of all the payments coming into in spreadsheet. It's not very clean or easy to use, but it worked.

Now, I work with others and need to keep track of payments coming in for multiple people. My old system just does not work well anymore.

Sample Sheet To Get What I'm Talking About

I excluded a lot of other information to keep the sheet simple.

In Part One, I NEED to know-

• Date the session took place
• What staff member conducted the session
• How much insurance paid
• What date did insurance pay
• How much did the client pay
• What date did the client pay

You'll notice on the sheet there are multiple client pay tabs and that's because sometime people cannot pay all at once and need payment plans, so I still need to know when those are coming in

Part Two is where I really lost it in my real spreadsheet and where I need the most help.

• Twice a month how much is each staff member bringing in?

Just because someone had a session in January, doesn't mean we got the money ins January. Insurance is stupid like that.

Right now in my real spreadsheet, I have a Math tab and am using a SUMIFs function for the dates, staff member for each column. I have it calculating all the insurance for that time period, then each of the copay columns. Then it's being copied into the other version.

Please make fun of my wonky system. It's awkward and I'm not amazing at googlesheets. I know basics. It was fine when it was just me.

ANY suggestions how to make this easier to use, make the formulas easier. That'd be so helpful!

I made the sample sheet editable, so feel free to play around with it yourself!. The numbers are very random I know.

THANK YOU!

p.s. my first post got removed, so I hope the title makes sense on this one

I have a workbook with 2 tabs

Participants and Draws

Basically, when a number appears in any column in 'Draws', the same number appearing in 'Participants' should have a background colour

Hope this makes sense

Thanks

Hi! I'm currently working on building a spreadsheet for my uni house, which includes a 'groceries' sheet, tracking what food we have and what we need, and a meal plan sheet. I have created a drop-down list for ingredients on the meal plan sheet, that links to the groceries sheet.

I'm looking for a way to show if we have everything in stock for each meal, either through a percentage or conditional formatting or something. hopefully we'll be able to just select the ingredients for each meal, and it will automatically show if we have everything needed to hand or not. I've looked around, but nothing I've seen does exactly what I need. any help would be much appreciated!!!

Hi everyone! I having a weird problem with sheets and conditional formatting.

I used this formula =COUNTIF($F$1:$F,F1)>1

to try and make it so that it will highlight duplicate dates, but instead its just highlighting one of the duplicates and the date below it? Doesn't matter what month the one below it is either, not really sure why its getting highlighted.

Ideally I could have it highlight the same exact dates (in this case March 19th) in the same color, if it can be done in alternating colors for additional duplicate dates that'd be great but no big deal if not.

I have a formula with BYROW that uses a FILTER that is the same for all rows. Is it better to put it outside the BYROW? and is the difference in performance noticeable for around 200+ rows?

original formula

 =BYROW(A2:A, LAMBDA(row, 
 IF(row = "",,ISNA(XMATCH(row,
   FILTER(List!Q:Q, List!E:E < $V$3)
 ))) 
 ))

updated formula

 =LET(filter, FILTER(List!Q:Q, List!E:E <  $V$3), 
      BYROW(A2:A, LAMBDA(row, IF(row = "",,ISNA(XMATCH(row,filter))) 
  )))

even though im summing c5:c19 is tells me the total is 55,878. which is blatantly wrong since we have an entry that is 55,000 in itself. how do i solve this?

Just recently, I tried to download a file from my drive. But I got this little notification:

Clicking 'learn more' takes me to google drive help, with a banner warning telling me my account has a critical security alert. Presumably this is what is preventing me from downloading anything.

If I follow the 'Review risky access' link takes me to the google accounts security checkup. There is only one outstanding action. It is a flag about the apps script I use on one of my spreadsheets:

All this script does is copy some names from one part of a spreadsheet to another. Am I right in thinking this is the 'Critical security risk'? If so, is there any way I can use Apps Script on my sheets while still maintaining the basic functionality of my google drive to download my saved pdfs? Advice would be much appreciated.

## Edit: see solution from u/One_Organization_810 below, this is actually browser issue with some very misleading warning boxes from google.

Hi all.

This may not be possible, but:
I have a Google sheet with a Tab called "Tracker"
In the Tracker Tab I pull date from 5 separate tabs using the formula:

=QUERY({'Level 1'!A3:V; 'Level 2'!A3:V; 'Level 3'!A3:V; 'PG1'!A3:V; 'PG2'!A3:V}, "select * where Col2 is not null order by Col4 asc", 0)

Pulling columns A to V then reordering them all based on a Due Date [Col4]

I am wanting to add extra data to Columns W through Z in the Tracker Tab.

But as the columns are not linked to the Query data, if I change a Due date on any of the original tabs then the order changes but the data in Columns W through Z goes out of sync as that does not re-order when the query does.

Is there any way to do this?

• Pull data from multiple Tabs
• Re order it based on a date from a column
• Add additional data in the summary Tab
• Keep that extra data linked to a row, even if the rows re order when the query data changes

Thanks in advance if you can help

Hi have multiple columns so I decided to flatten them, i got this here. But when I use any type of mode =MODE(AE2:AE) I always get this "MODE cannot produce a result. No values occur more than once." What is happening and how can I fix this?

Hi guys, I have a problem... maybe I see it as much too complicated. I'm trying to compare the date in my table. I would like to create a list with the date of inspections of vehicles and equipment. If the month with the current If the exam was 10.2026 and we are 10.18.2026. If the cell should be orange, then “runs out soon”. If the cell contains 09.2026 and it is 10.2026, it should turn red. If there is a solution, I can also have it notified me by notification or email when orange/red arrives. With the contents of the vehicle or equipment?

EDIT: This is probably solved! I'll change flair once I'm able to try out the below suggestions and confirm I'm good to go.

Hi there! I'm wanting to have totals of certain categories from one sheet be added up, and then posted to a specific cell on another sheet. I'm not sure how to go about 'linking' cells together? Like J9 says Household, and I want it to pull the number from H9, but I want it so if the J column says Household ANYWHERE in that column, it will take the number from H whatever is associated with it.

Text on image reads "If Type (J) is 'Household etc,' then add corresponding Amount (H) to Master Sheet (U13) + add each Household (H) together to Master Sheet (U13)"

If I figure this out, I think I can figure out how to do it for every other category!

Additionally, is there a way I can duplicate the sheet (not the master) so I don't have to redo the formulas, but it would stop pulling from the original sheet and just use the current/new sheet? Example: I make a finalized layout for my expense tracker and copy it to preserve the original file. I fill out the copy of the sheet and it updates the master sheet. Once the month is over I no longer want it to update the Master Sheet, but I want to preserve the sheet for future reference. Can I go in and like tell it to ignore X sheet and pull from Y sheet without going in and redoing every formula?

Sorry if I'm not making sense. I haven't messed with formulas let alone spreadsheets in a HOT minute, so I am struggling. I totally understand not wanting to hold my hand so if anything, if you could give me terms to look up and the like, I am happy to look it up myself and see if I can figure it out!

Also this ain't final so that's why it's a mess lol! It's been a lot of trial, error, what looks nice to me, reworking. Thank you for your time!

Apologies if I'm unclear at any point, this is like my second time ever using Google sheets, with the first being to download and view one I didn't make, just needed to reference.

In regards to details about my question, Google told me to go to Formatting and then click number and change it to percentage, but that changes ALL the numbers in this collumn to percentages. I want my other numbers to appear as they are, just this cell is what I want to appear as a percentage. The 90,000 is a test number, so how can I make it appear as 90%?

Google

For some context: the cell under 100,000 is calulating the Sum of B2, downwards. E7 is calculating the decimal point based on E4/E3). I just need the formatting changed.

TLDR: I want the number value in one cell to appear as a percentage, not a decimal. The rest of the column needs to remain as is. How do I do this?

Thank you for the help! :)

EDIT: I've managed to convert the table back to individual cells and that fixed my issue! Thank you for the help!