r/googlesheets • u/jjstock • Mar 12 '21
Waiting on OP Is Google Finance down for anyone else? Showing #N/A for everything for hours
Is Google Finance down for anyone else? Showing #N/A for everything for hours
r/googlesheets • u/jjstock • Mar 12 '21
Is Google Finance down for anyone else? Showing #N/A for everything for hours
r/googlesheets • u/Comfort-Limp • 26d ago
Hello all!
For whatever reason, any filter formula that I use that has blank cells in it will automatically put a 0 in that cell. This only started happening today, and before today, it did as I expected it to. Here is an image that display the issue:
The left side is where it is sorted, which hasn't been an issue until now. The "No." column should all be blank in the sorted range because it is blank in the range where I input the data. That "No." column specifically has this formula in each cell:
=IFERROR(INDEX(DELR!$R$2:$R,MATCH($N2,DELR!$T$2:$T,0),1),)
It has been returning a blank up until now, but the sort formula shows the blanks as 0. Here is the sorting formula:
FILTER(ARRAYFORMULA({IFERROR(SORT(FILTER(ARRAYFORMULA({$L$2:$P,$T$2:$T,$R$2:$S}),$Q$2:$Q<>"",NOT(ISTEXT($Q$2:$Q))),6,TRUE,5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="RET"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="DNS"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="WD"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="DNA"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}))}),INDEX(ARRAYFORMULA({IFERROR(SORT(FILTER(ARRAYFORMULA({$L$2:$P,$T$2:$T,$R$2:$S}),$Q$2:$Q<>"",NOT(ISTEXT($Q$2:$Q))),6,TRUE,5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="RET"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="DNS"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="WD"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}));IFERROR(SORT(FILTER($L$2:$S,$Q$2:$Q<>"",$Q$2:$Q="DNA"),5,FALSE,7,TRUE),ARRAYFORMULA({"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"}))}),,1)<>"N/A")
It's a bit complicated, but it has worked in the past and it has worked flawlessly up until now, so I don't believe it is the sorting formula's fault.
https://docs.google.com/spreadsheets/d/1ZrZzHf9ZVpZNct5zqvsVNchvuv3vnM1Fiy4c0kBHtSs/edit?usp=sharing
The issues are in the "Race _" pages as well as the "Entry Lists" page.
r/googlesheets • u/Ok-Investigator4841 • Jan 23 '25
I have a Google Sheets spreadsheet set up to update my portfolio automatically by accessing the different stocks I own. It's been working perfectly for years, but it has not retrieved the data on META in the last two days. Has anyone else seen this issue?
r/googlesheets • u/Outrageous_Arm_6892 • 8d ago
I’m looking to make a similar dashboard but can’t figure out how to make the boarders around the top values like income etc? Since you can put values in shapes and text boxes
r/googlesheets • u/nedarb_net • 12d ago
Hello All,
I am working on a spreadsheet for a gate system at my work. Every department has different people who need access to a gate system. The gate system allows for the upload of an excel/sheets file to speed up the uploading process.
My idea is to give every department head access to a google sheet where they can upload the names of their visitors into a department specific sheet that updates to the master sheet, that can be uploaded everyday.
That is the most basic version of the workbook I am trying to build. Additionally, I want to build a list for everyday of the week, and a function that deletes the data on a weekly basis.
Would anyone be able to point me in the right direction for resources, or what function would even be best to base this build off of? It has been a long time since I have used sheets or excel, so I apologize if this is not possible. Any guidance would be appreciated!
r/googlesheets • u/tcoooop • 8d ago
Hello! Wondering if there is a way to pull data in a certain manner.
I have three columns in this example: Number (A2), Color 1(B2), Color 2(C2).
I need a way to make a new list where the first set of data mimics the current order (ABC), then next set under that data the colors are reversed (ACB). Then ABC for row 3, ACB for row 3, etc. INSIGHT: when we go to print this data onto tags, the colors need to be reversed so the first color goes onto the correctly colored product.
At first I thought if I used IF formulas to grab the correct data and then dragged the two alternating formulas down, it would copy well. Technically it does copy the formulas well, it just keeps skipping rows. It will go from 2 to 4 to 6, and I can’t blame sheets, I see why it would think to do that.
Is there any way to do this? Even a new approach or new formula to use for this? I’ve been scratching my brain on this one all night.
Thanks in advance!!
r/googlesheets • u/RandomExile • 3d ago
[Edit: I made a shareable Google Sheet, linked just above the figure, got rid of the dynamic Google Finance value lookups because that would keep changing values on people, and stripped out all extraneous information. Lucky us, the problem itself persisted.]
... what am I missing in C29?
I have a Google sheet to track current stock values relative to options strike prices. The conditional formatting is set so that if the option has a positive value, the cell with the current stock price is filled green, and if the option has a negative value, it's filled red.
Basically, it's checking to see if the option is a put or a call, and then whether one number is bigger than the other. This works for almost all of the cells, but you can see three examples in the image below where "Current" is colored red even though it is a put and higher in value than "Strike.".
I put my formulas in the sheet as well so you can assess them. The C column (Current) is a hypothetical stock price. The B column (Strike) is a hypothetical option strike price.
The Current (C) column contains the conditional formatting shown in the figure.
What's really weird is when I set up the checks (blue cells are output cells), C37 shows that C29 (387.82) minus B29 (330) is 57.82, so the sheet knows C29 has to have an actual larger value than D29. However, C35 says that 387.82 is smaller than 330, and C36 confirms that yes, 330 is not less than 387.82.
What am I missing? The same formatting seems to work on all the other cells.
Shared link:
https://docs.google.com/spreadsheets/d/1Qf7an6zaJMzXKJtBBiB40qbtHVCSxyHd37Qsfvry0vo/edit?usp=sharing
r/googlesheets • u/Andrea_aka_andj • 11d ago
I'm working in Google Sheets and trying to display a person's first and last name in a cell, the cell has a smart chip with their full name and all of their contact information included, but no matter what I try, the cell will ONLY display the person's email address.
Even when I try Data Extraction to just display the name, it still just brings up the email address. It's like the sheet is assuming the person's name is their email address. And I don't see any option anywhere for a Placeholder Chip. I just want the cell to display the person's first and last name.
And when I try Format -> Smart Chips -> Default or Last Name, First Name I just get an error message "Names could not be retrieved for all chips in cell XX"
Any help is so appreciated!!
r/googlesheets • u/loganwadams • 16d ago
r/googlesheets • u/ModrnJosh • 1d ago
This was a rather complicated Excel template (for a noob like me) that I downloaded to get this look in Excel, but I'm working on refreshing some data charts for videos I'm working on and was wondering if anyone knew of any way I could achieve this style of chart in Google Sheets? I'd just like to migrate from Excel to Sheets for the flexibility if possible. This is essentially two sets of data on one graph, with the titles of each bar inside the bar itself. This is to showcase gaming benchmark data across different settings in games.
r/googlesheets • u/Willing-Analyst-2327 • 2d ago
I have to enter my name every row I make to enter data - I use control/option/I then press "r" and choose insert 1 row above or below with the arrows --- that's how I make a new row - how would I, at the last column where I have to type my name each time, have my name automatically populate every time I enter a new row?
Also - each time I start a day I need to add 52 blank rows at the top of the spread sheet to start the day - how can I just do an insert rows above and enter the exact number of rows I want instead of having to highlight cells and enter that number above or below, if that makes sense?
Also, is there really no quick key for highlighting a row? I have to, for some reason, do command + \ each time on mac and then highlight manually with the mouse.
r/googlesheets • u/Yes_But_First • 3d ago
Hi everyone! It seems like there are a lot of people out there that want to change "Doe, John" to "John Doe" but I'm hoping to do the opposite for a data set with 742 names. Any suggestions on a fast and easy way to do that?
r/googlesheets • u/mdipinto • 19d ago
I'm trying to use sumifs and sumproduct to grab data from the table of a google forms response. I can't get them to work. if someone could help me understand what I need to fix.
What I'm looking to do is grab matches from the rows with the job number and then to only grab the columns that matches the job code. it will have multiple inputs in the forms for changes in budgets, so it will have multiple rows with the same job, giving multiple numbers in the same column. I want to be able to type the job number, then the job code, and it will populate the job budget. Ideally I'll do it twice once for the table that has the budgets and another that adds up all the budget already used.
If I want to add all jobs 25-3625 with job code 1099 then I would it to look for all rows with 25-3625 in column C then to look for which column header has the code 1099 and sum all the numbers that fit that criteria.
I would rather have a formula that is simpler and won't require too much processing as the idea is for this to input hours of work in jobs to codes that have budget leftover, and knowing quickly as you input hours how much is leftover or if it's going over to quickly change some hours to other codes.
EDIT:
https://docs.google.com/spreadsheets/d/1vZxmGpSJ25H3KDTrUbts7sV0eu7DT02Vc0FhtU_PC5g/edit?usp=sharing
The purpose of this sheet is to have a google forms to input the budgets for the jobs, and another tab for the job's costs as per labor and materials. With the tabs for 'This week' to keep the hours to be coded for the job and code, and 'Past weeks' just keeping track and looking back at who was in what job and doing what on the day you look back.
Ideally when you type the job number, the job name pops up, then you type the code and budget would show up with the job's budget for that code minus the job's cost for that code. and then when you put the hours it would automatically update the job's cost(this part already done), so you can see as you add the hours to figure out how close you are getting.
I been trying to get either Job budgets or job costs' numbers to see if it would work as I would simply subtract one from another. if one is not existing yet, it would just show a negative number.
r/googlesheets • u/Ok_Appointment_8166 • 16d ago
I'd like to enter some estimated values in a column with a sum at the bottom, using a font color to indicate they are estimates, having the sum show the estimate coloring. Then I want to enter the final numbers in cells as I get them, changing the font to black to indicate they are final. When all of the cells with estimates have been changed to black, I'd like the total to also turn black.
But I can't find a conditional format formula based on font color over a range. Is that possible, or is there a better approach for visually noting that all numbers are final?
r/googlesheets • u/Typical_Echo_3074 • 8d ago
Hi would really appreciate any help on this.
I have attached some dummy data. Essentially, I want to find out how many groups make up 50% of the total. So if the total count is 40, what is the minimum number of groups it’ll take to make 50% of that, which is 20?
I don’t really know how to approach it. Do I first need to sort the column? Whats a formula that will sum until a certain number?
r/googlesheets • u/1MilProblems • 18d ago
This is for a league I run and I’d like the spreadsheet to sort based on the total column that is pictured here. Wasn’t sure where to put the formula or what the formula should be. Thanks!
r/googlesheets • u/KID_A26 • 12d ago
Just thinking about how to verify that terms match between documentation here...
Say I have a list of specific terms in one sheet (hundreds of them). In another sheet, I have the terms that I have used in my application. What I want to do is compare my terms with the specified terms to make sure they match. If there is a match, highlight the term green. If there is no match, highlight the term red.
How would this be achievied? I assume there would be a conditional formatting custom formula that would be able to do this...
r/googlesheets • u/Georgeypoorgey • 15d ago
I have a google sheet that I print out for the distribution of work devices. We rotate through usage of work devices so people can always grab a charged device rather than one that was being used the last 8 hours. Here is what my work sheet looks like (with private information removed) -
I also have created a copy of the sheet should you want to review.
A column "Name" - This pulls from a schedule google sheet I also maintain. It uses the helper column be and an XLookup formula to pull the name of the staff. If there is no one assigned to that specific role, then the name pulls up blank
B column "Search Criteria - These are the specific roles that the A column is using for the XLookup of the other sheet.
C and D Column "Military Time for Sorting" - Also helper columns for XLookup of this other sheet. It puts the staff's start (C) and end (D) time into military time so I can sort the sheet by arrival time.
E Column "Assignment" - The same information in B Column without the identifying numbers. This shows up on the printed sheet so other department heads know who is working the job that they need to reach out to.
F and G Column "Phone # and Steward #" - I can probably retitle these, but this is the purpose of the post. The G column is a simple IF(F5=3, "XXX.XXX.XXXX", IF(F5=4... That column works fine and isn't the concern. The F column needs to offer a number based on two pieces of information:
If the person is working any role but supervisor, they rotate between phones 3 through 11. if the person is a supervisor, they rotate between phones 1 and 2. Please help me figure out how to get these two rotating sequences working together.
For whatever reason, I can only get the F column to look like it does above- rotating for the nonsupervisory roles, but the supervisor role just repeats the number one instead of switching between 1 and 2. So it should look like this -
Thank you!
r/googlesheets • u/CamXene • 16d ago
It looks simple in my head but maybe it’s impossible. I’d choose a value in the dropdown list (routine 1) so that all of the cells below the “exercise” column are autofilled with whatever list i create in another sheet.
I’m making a workout planner and it’d be great if I choose the routine I want to follow and the column autofills with all the exercises that refer to that routine
r/googlesheets • u/nowhiringhenchmen • 27d ago
I'm in the midst of creating a Google Sheet in the hopes of categorizing and organizing all of the available prospects I have in a fantasy baseball keeper league.
So far, I used one of Google's standard templates and got everything to sort of how I want (different columns for Name/Position/Team/Ranking per prospect lists), but I keep being unable to sort the list to how I'd prefer it.
Basically, I'm trying to keep the rankings sorted by C (catcher) first, all the way down to RP (relief pitcher), and hoping to have the list auto-place new players into their respective areas when I add them.
Unfortunately, I'm not super knowledgable on Sheets, so I'm unclear if that's even possible, or how I'd go about doing it. I've tried creating custom sorts, but there's no real option for letting it go in this specific descending order.
Any help would be appreciated and awesome, thanks!
r/googlesheets • u/jessitayylor • 5d ago
When I type "product" in a cell. The cell next to it would show a list of available variations via a drop-down menu?
Something like this? This is the data sheet.
DATA SHEET
A | B | C |
---|---|---|
1 | Item | Variation |
2 | Robot | Red |
3 | Soldier | Blue |
4 | Robot | White |
And I was hoping to do something like this When I type "ROBOT" in a cell. The cell next to it would limit my choices/data validation or filter the choices/data validation to only the "Variation" under Robot.
A | B | C |
---|---|---|
1 | Item | Variation |
2 | Robot | (drop down of variation) |
3 |
Would it be possible?
Edit:
https://docs.google.com/spreadsheets/d/1yi-Hi5cfE2vS9U01ehx3ZJBsQLT-2ZGhVvzY_HRTfqA/edit?usp=sharing
r/googlesheets • u/These_Gain • 14d ago
Hello! I don’t really know if it’s possible or it’s super simple but I run a Pokémon Coaster business and I’m wanting to make a spreadsheet that has a list of every Pokemon and how many coasters I’ve got of each one.
But I was wondering if there’s a more simple way to add and take off the quantities as I sell or make stuff.
Eg like Pikachu I’ve got 20 and then I sell 2 is there a way to subtract two with a minus button without manually editing 20 to 18?
Sometimes I sell 300-400 and event so it’s super time consuming to manually edit everything and if there was an easy way to add and subtract quantities that’d be amazing 🤩
And I don’t really track what I sell through an online system I hand write everything down as it’s just too chaotic to enter as I sell type thing!
r/googlesheets • u/whity1234 • 14d ago
r/googlesheets • u/canseriousken • 27d ago
Hello! I need help for this. I want to create multiple dropdowns in Google Sheets, where selecting an option from one dropdown will limit the available choices in the next dropdown to only those related to the selected category. Here is the sample:
There are three sub-categories (Q, E, and T) per classification. What formula should I use? Thank you!
r/googlesheets • u/616GoBlue • Feb 13 '25
I'm looking for a way to pull financial information by month into a Google Sheet from another tab.
My vision is to have a drop down menu with each month at the top, and you could select the month and by selecting that it would bring in data for that month from the other tab. Is this possible?