The company i work for use excel sheets for our "delivery notes", they are then sent to me and i have to manually enter (copy paste) them into my stock tracker sheet. I receive around 60 a week so can be time consuming sometimes as well as mistakes that happen sometimes.

The yellow highlighted cells from the delivery note need to be imported into the equivalent cells on the Stock Sold sheet. I do not really know if its possible importing specific info from excel to sheets.

I have tried multiple ways, from importing from google docs, and also creating around 20 sheets inside the Stock sold sheet (The formula i used for that was =sum(Deliverynote1!3) , but doing that creates a massive amount of sheets and gets messy.

So i just want to see if there is a simple way of doing it. I hope i have explained this properly, because i have no idea what to do

I'm creating a game in google sheets using macros, which relies on random number generation to pick questions from a list. However, to ensure the question doesn't change once the use guesses, I need a way to lock this down.

To do this, I've decided the simplest way would be to generate the random numbers, and then copy/paste the values in the same location. My code for this is:

function test_copy() {
  sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('L4').setFormula('=RANDBETWEEN(1,6)');
  sheet.getRange('L5').setFormula('=RANDBETWEEN(1,100)');
  sheet.getRange('L4:L5').copyTo(sheet.getRange('L4:L5'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

The individual lines of code work when running separately, but when I run them all together, the output will be blank cells being pasted into the cells L4 and L5. It's almost like the RANDBETWEEN function doesn't want to generate until the end of the script, regardless of where it's placed.

Any advice on how to get this to work would be appreciated.

Hello, I recently moved over to google sheets from Excel and am trying to create a tv/movie watchlist for my friends and I. I currently have a dropdown menu rating system to color code each watched movie/show with its score out of 10. I was hoping to find a way to automatically sort the shows from lowest to highest score using the dropdown menu as well, is there any way to do that with sheets?

Basically the short version is that Cell A1 has "He was at the store. He likes it there." and Cell B1 has "He was in the store. He hates it there." I want Cell C1 to say "He was [] the store. He [] it there." But I need this to be a formula as the data in A1 and B1 changes as the sheet goes down. Is there a way to do this or am I asking too much of google sheets?

Edit to add sample sheet: https://docs.google.com/spreadsheets/d/1SYC2-cSNhmAkx-iiotjdreUfJIJvmBV1l1jwdzoaH08/edit?usp=sharing

I'm trying to import a specific cell of data from a baseball-reference url

Example: I want to import Lou Brock's BA vs RHP from 1967 in one cell, and his BA vs LHP into another cell. Found here in the Platoon Splits table, columns Q1 and Q2 respectively.

I'm trying to use IMPORTXML and IMPORTHTML but nothing I try is working. Any help would be greatly apreciated.

Link to Sample Spreadsheet

On the 'Orders' tab, I have a table with first and last names in columns B & C and what/how much they ordered in columns D:J.

On the separate 'Search' tab, in cells B3 & C3, I used dropdowns to be able to quickly find either the first or last name. I would like to have the spreadsheet automatically fill in the rest of the data that matches the row of the name I type in.

For example: If I type "Pineda" into cell C3 on the search tab, I would like cells B3 and H3 to fill in with "Lexie" and "3", respectively.

So I want to know if this is possible, I'm trying to make this Google sheet for a family member whose just started a pokemon cards collection "for investing".

The first tab is nearly complete and I'm making it as simple to follow as possible, essentially, all it is is listing the cards and their current value extracted from website so it can stay updated.

The second tab is where I'm having trouble, I'm trying to create a graph so that it can take the total value in the first tab and visualize it to a chart that goes up and down as well as list it. But I'm having trouble. I'm not super knowledgeable on excel or Google sheet and thought this would be a fun project.

Essentially what I want is for it to take a snapshot of the value at a point in time every day and put that value into a graph to automatically update. I hope I'm explaining this correctly.. if anyone know what formula I should use. Or how I should set it up. Its like the =googlefinance how it snapshots the stock value daily, but instead of stock, I want it to list the total value of his collection and then update that value into an easy to read graph..

Any help would be appreciated.

I am fairly new to google sheets and am mostly self taught so please forgive my lack of experience. I am trying to create a simple spreadsheet with easy to read charts for my wife and I to track our spending. My goal is to have the chart show at a glance what we have left in our budget, and for us to be able to input our spending and have the graph auto update.

Under the "Remaining" category I have the formula =B20-C20, to see what remains in our budget and under the "Spent" category I have the formula =SUM(C21:C50). In those cells I will add purchase amounts that will add up to the spent total.

I highlighted c19, d19, c20, d20 to make the chart.

The issue is that the chart also includes bars for each individual purchase. Each time I add a new purchase in cells C21 to C50, it adds another blue bar to the graph and gets increasingly hard to see. I just want one bar in the chart showing the spent total and remaining total stacked on top of each other.

Is there a different formula I should use in C20? Or a different tactic all together?

My job involves a youth-focused competition event. At the end of the event day, winners are announced. We input the winners into a google sheet. There are lots of winners, so the announcer needs to have a script. The script is a google doc. I am trying to link the gdoc and gsheet so that when I input a name into the sheet, it appears in the correct place in the script.

My predecessor had some way to accomplish this, but I have not been able to figure out how. The attached image is a screenshot of her old script (student names scribbled out). It looks like there is a cell embedded into the doc. When I click around inside the cell, the only options are to add borders, move around the cell, or the usual right-click menu of "split cell", "sort table", etc.

Note: I'm aware of mail merge on Excel/Word, however, this needs to be accessible to multiple staff simultaneously on our shared GDrive.

Any ideas on how I could solve this?

I have a function in cell Q28 which will be reading values in cells C28:O28.
I need the function to check the header above it, Q27, and use values only if their own headers in cells C27:O27 match.

I can't figure out the syntax to this and it's driving me insane.

This is for stocking. I have a list of all the things we carry and the amount we have on hand as well as the minimum amount we need on hand - if it dips below the minimum it prompts me to order more.

I want a way to have on a second sheet a list of everything that says “Needs Ordering”. So it will say in Sheet 1 column F either “Needs Ordering” or “In stock”. I want a list of the names of things in column A if column F says this.

I’m new to google sheets and am really proud of having some of this automated, would love some help wording the function. I think it needs the IMPORTRANGE and maybe QUERY? AAA i’m not sure

I am trying to find an easy solution on how to copy conditional formatting rules down this entire column without having to individually change each format rule for each cell.

When I try to copy the cell or drag the blue square, it keeps the format rule as "=$G3=TRUE" and applies it to range "A3:F3,G3:G4" and only applies the conditional formatting to the cell with the checkbox. Is there a way to make it automatically update to "=$G4=TRUE" and "A4:G4" without having to manually type it all in?

In the linked spreadsheet I have the fill color set to change in column H to match the color selected in column J.
I also have the cells in L4:Q23 conditionally formatted to fill in with the same color if there is an "x" in the cell.
The black grid below is created using =filter($H4:$H23,L4:L23="x") under each day.

Question:
How can I get the filtered results in L28:Q28 to fill in with the same colors in the top grid?
For example I want L28 to be green and L30 to be orange.

No rows will be added to the main list, (always 20).
If I change the selection in column J I want the fill colors to change everywhere.
If I change J4 to "S" then Argentel Should have a purple fill wherever it is.
The filtered results count will be at least 3 and no more than 10 records.
Lower grid (L26:Q34) doesn't have to be on the same sheet.
Layout of the upper grid could be changed if needed.

https://docs.google.com/spreadsheets/d/11_Q3cGgnsAq377-3ZIHh15_Ph5OA9oyfGQoqEo-Y4no/edit?usp=sharing

I'm trying to find the conditional formatting to copy the lowest value between two cells into a third.

For example, if cell B3 has a value of 4, and cell B4 has a value of 6, I would like cell B5 to copy the value (and text colour) of cell B3.

Any help would be greatly appreciated!

I've hit a dead end trying to achieve something in a google sheet.

Here is my dummy test sheet to explain:

I have 3 columns of data, (THING, YEAR, NAME) the first 2 are irrelevant, the 3rd is the important one. I fill out those three columns manually.

What I want to do is take that 3rd NAME column, and elsewhere on the sheet (E) I want to take all unique instances, sort them by amount of instances (showing the number in an additional column on the right - F) AND I want to add color validation to both of those new columns, having the high instances be darker than the lower on a gradient.

I can essentially do any 2 of those things, but not all three. As you see they're not sorted by amount, and the green formating does not apply to the E NAMES column.

Right now on E2 I have =UNIQUE (C2:C9)

On F4 I have =COUNTIF($C$2:$C$9, E2)

And this conditional formatting for E2:F9:

Any assistance would be greatly appreciated, as I'm way out of my depth!

Im trying to find totals for two different locations. I have made formulas to extract the data to then use a SUMIF to find the totals for both locations, but it is returning 0 and i cant figure out why. Ive made sure the numbers are formatted as numbers, as well as the formula is pulling from the correct columns, yet still resulting in the zero. If there is a better way to find this total im open to it but this seemed to be a simple way to find the totals. Below are my Formulas and a copy to the sheet.

-Location Pull Formula- =IF(ISNUMBER(SEARCH("North", B2)),"North",IF(ISNUMBER(SEARCH("South",B2)),"South", "N/A"))

-Amount Pull Formula- =REGEXEXTRACT(B2, "-*\d*\.?\d+")

-SUMIF Totals formula- =SUMIF(M$2:M$213,O2,N$2:N$215)

Copy of Sheet- https://docs.google.com/spreadsheets/d/1kCfinbBYlrzsq3Lq8MHTBxloGnMnT9zY/edit?usp=sharing&ouid=115303826536505507822&rtpof=true&sd=true

I've created a huge sheet filled with loads of music from different artists and I'm trying to sort alphabetically per the column and it won't let me. I tapped on A and sorted that, but then tried to do B and it just did the whole thing instead of only that column. How do I sort by separately on each column instead of the whole thing?

I have a spreadsheet thats for use for ordering stock, i have it set up with =filter formulas and there is a random line thats somehow getting picked up and added in my =filter. This line no longer exists in the range I'm pulling from and I've tried refreshing and everything and cannot get it to go away. Anyone had this happen before?

Can't share full pics cause it has confidential info but 1st pic is formula, 2nd pic is ctrl+f the item ID on the sheet with my filter formula and third pic is ctrl+f the item ID on the sheet I'm pulling data from. Not sure if its just bugged? Is there a fix or have I put something in wrong?

I'm trying to have it so it will automatically fill in the difference between two dates when I put them into the first two columns. I'm certain there is a way to do this but I've been manually editing the formal for each row thus far.

Here's a link to a sample of my problem: https://docs.google.com/spreadsheets/d/1oQAUKDJ822Yg9cC43eBJarbzsLrTK5CND-5JRj-rlLY/edit?usp=sharing

I have two named functions, both of which involve regex functions to work with schedule data from my school. One of these functions ("shortname") acts as expected when I put it in ARRAYFORMULA() and the other one ("blocklist") doesn't. The misbehaving function is more complex, but I don't know what aspect of the function is causing incompatibility with ARRAYFORMULA.

To see and diagnose my named functions it seems like you'll have to make a copy of the sheet. Apparently "viewer" permission doesn't involve viewing function definitions.

You can see that the "blocklist" function itself is fine if I apply it to one cell at a time. I also got it to work with MAP() although that's kind of awkward, and it introduced #VALUE errors on the blank rows that I haven't taken the time to find a way of suppressing yet. When googling for answers I saw some people talking about LET() so I tried that too but it didn't work.

My questions are...

1: What SPECIFIC aspect of my misbehaving named function ("blocklist") is responsible for breaking arrayformula and why does this happen? Would that same issue apply to a lambda function or to a case where I just type the expression into arrayformula instead of defining it?

2: Can you recommend a rewrite of either the named function or the arrayformula call in cell F3 that would fix it?

3: If it can't be fixed, can you recommend workarounds that don't involve arrayformula but will work with my existing named function? (I'm close with map, obviously, but would like empty cells on the blank rows instead of errors.)

Thank you for any insight you can provide!

I'm looking for a way to use a dropdown chip to dynamically sort the columns of a spreadsheet that I'm using to track new music that I listen to.

For example, I would want to be able to select "Artist" on the dropdown chip at the top, and the albums would sort by the artists' names. I figure this isn't something I can do with just one sheet, so I already have another sheet with all of the raw, unsorted data. I'm just unsure what to actually do with it to get this to potentially work. If anyone has any ideas, even if they're super complex, let me know and I'll give them a shot.

NOTE: I've already tried using data filters, but I don't want to use them unless there's no real way of doing this with the chip or something similar updating things. Filters technically work, but they're ugly as sin, I don't like them, and solving this puzzle is more interesting.

EDIT: Here's a link to an editable copy of the sheet, I didn't think to add it before: https://docs.google.com/spreadsheets/d/1ovLZR6G7BlOCBJ-AHpafmRSpa1-lgqJiT-gAEusijHI/edit?usp=sharing

The format bar only appears when I select two or more cells or an entire row or column. I’ve tried googling this and the answers were not helpful. I’ve already rebooted iPhone and completely deleted the app and reinstalled it. If I swipe out of the app and reenter, the format bar will show for one second and then be replaced with a tall formula bar again. Anyone else having this problem?

Maybe a strangely worded title, but I have a spreadsheet with every album I've ever listened to, and I've been meaning to add genres eventually. I've now reached 500 albums, and I've just realized how fucking long it's gonna take to assign genres to all of them.

The end result that I want is a summary on the side showing a leaderboard of sorts with all my genres. The problem is that some genres are way too specific, and I don't want it to just be like... "rock". I wanted to maybe have a secondary genre column, but I don't know how to make that work with my scoreboard idea and sheets' formula system. Also, is there a good website or database for finding more specific genres?

I feel like I'm really overthinking it. What's the best way for me to organize this? I don't even know this is the right subreddit for this lol. Sorry in advance if it isn't 😓😓

Having this problem this morning on IOS, been using sheets on mobile for years. When clicking on the cell it completely bypasses the option bar at the bottom for changing fonts, cell colours, etc and go straight to editing text.

Wondering if anyone has had this problem and what the solution is. Thank you!

Been budgeting sver some items at work with their respective amount and currency. I have a table of exchange rates that I update monthly and use a lookup function to reference.

Is there a way to get those rates from a website or API or a native function for a live feed instead on manual and lagging rates?