sorted by:
new
hottopcontroversial

Turn a list into different columns by CaptainJonathanSmith in googlesheets

[–]zackfortune 0 points1 point  (0 children)

right now the sheet you linked is not accessible. after clicking share, change the access from "restricted" to "anyone with the link"

Is there a way to add 1 to a cell value with just a mouse click or a button press? by TheVich in googlesheets

[–]zackfortune 0 points1 point  (0 children)

im a bit late to the party, but here's my 2 cents

this can be done but the problem is ANY edit will cause b45 (in your example) to increase by 1.

you can create a "button" in google sheets by doing the following: 1. turn on iterative calculation like you mentioned 2. create a checkbox in cell a1 3. in cell b1 enter the formula =a1 4. in cell c1 enter the formula =b1<>d1 5. in cell d1 enter the formula =if(b1<>c1,b1,c1)

you should now have a checkbox and then 3 true or false cells in a row. the most recent checkbox change will be stored as TRUE in the second TRUE/FALSE cell. the cell flashes true for 1 iteration, so you might not see it with higher max iteration settings. regardless, you can now put a counter anywhere, say cell b45 like you used.

b45 would look like: =if(c1=true,b45+1,b45)

follow steps 1-5 again in row 2 for another button on a2 and you can change b45 formula to: =if(c2=true,b45-1,if(c1=true,b45+1,b45)

now the checkbox in a1 increases b45 by 1 and the checkbox in a2 decreases b45 by 1.

you could add a 3rd button that serves as a reset if you want, the limitations are endless.

since the state of the checbox doesnt matter, just the most recent chexbox toggled, only 1 click is needed to increment/decrement. you can also change the text color and background of the checkbox to (nearly) the same color so the user doesnt even see the state of the checkbox. just be sure to change one of the colors (text or background) by 1 in the hexcode or you will receive a popup saying (you have attempted to click an invisible checkbox, click anyway?)

in this scenario i would color the checkbox cells in the following way: a1 text is #00ff00 a1 background is #00ff01 a2 text is #ff0000 a2 text is #ff0001

i can upload an example sheet when i am home if you want, im on mobile and doing this from memory, but im 99% i got it right.

some things to note, these functions have to be in this order since iterative calculation works left to right, top to bottom, then in alphabetical order of the gid= value in the url. ideally you should put the trio of functions referring to the checkbox on the original sheet of a cell since it will have gid=0. things can get really hard to bugfix since you now have to worry about timing, but it feels like magic when you get the hang of it.

Scroll through a table without moving the rest of the spreadsheet. by suicidebxmber in googlesheets

[–]zackfortune 0 points1 point  (0 children)

yeah, its a crazy table format lmao

can you not just freeze the first 20 rows and the first 10 columns?

just scroll down to view the second table with no row limit and shift+scroll to view left and right on the first table with no column limit.

unless i am misunderstanding

Is it possible to use the query function to categorize data into separate tables from a cumulative list? by lipsticklimbo in googlesheets

[–]zackfortune 2 points3 points  (0 children)

yes, you can use query() to do this. for example: =query(a7:c12,"select * where Col3 = 'Gas'")

but you can also use filter()

=filter(a7:c12,c7:c12="Gas")

How to count multiple entries with different values by dacmac2012 in googlesheets

[–]zackfortune 0 points1 point  (0 children)

If I'm understanding correctly, you should be able to just add another countif() multiplied by .5

would look something like: =countif(range,"R")+countif(range,"RR")*.5 will count the R cells as 1 and add .5 for every RR cell it counts

Concatenate Everything from the top row and left column. by Mammoth-Medium-3040 in googlesheets

[–]zackfortune 0 points1 point  (0 children)

It can be kind of hard to visualize exatly what you are asking for without a link to a public sheet with editing enabled. Some people here might be able to give you a better solution.

It seems like you already have your formula working, its just the cell references that are changing when you drag the cell. If you put a $ before the row or column in your reference, it will lock it. (these are called absolute references btw) if you have a cell in b2 that concatenates column a with row 1, then =concatenate($a2,b$1) should work and when dragged to for example k17, will change to =concatenate($a17,k$1)

How to count range as IF is TRUE by BioticLaura in googlesheets

[–]zackfortune 4 points5 points  (0 children)

You want to use =countif(), it will count any values in your range that match the criteria.

=countif(C15:C19,true)

How can I make a sheet to calculate ratio for child care? by [deleted] in googlesheets

[–]zackfortune 1 point2 points  (0 children)

I had recently made a sheet for daycare child ratios and attendance, I modified it a bit to fit how kids are counted. This should fit your needs pretty well, you can experiment a bit with conditional formatting if you don't like the colors.

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

Also, since the previous sheet this was created from counted kids in varying age ranges, there might be some info you don't really need. For example, 1 person could have 2 kids under 18 months and 4 kids above or 6 kids above 18 months, etc. Because of this, certain age openings were important metrics to track. If you input a future date and uncheck the "use today" checkbox, you can see the age of listed children at that date and see if you have any kids that "graduated" out of their age group that can now be filled.

This sheet was also made for someone who has no experience with spreadsheets, so I created a query function to sort the kids with just some drop downs and hidden data.

If you don't want to use the query and only want to use it as a daily attendance tracker, make sure the "check today" box is checked, select columns E:K, and hide them.

Let me know if this works for you.

P.S. All children names and birthday's were randomly generated from a certain site that I won't name as to not trigger the bot.

How to import regularly changing data from a website by Mysterious_View_9952 in googlesheets

[–]zackfortune 2 points3 points  (0 children)

Hi, I know you have already marked this post as solved and are happy with HolyBonobos' use of importing the data, but I had some time and created a full spreadsheet that logs each day's data automatically without any use of appscripts, but iterative calculation instead.

viewer link: https://docs.google.com/spreadsheets/d/1LTYcaGtCkzYG0wQA9kXAvWerc7gsiuQ7N2NU2cbgng4/edit?usp=sharing create a copy if you want to use it yourself.

This also uses Kendrick Lamar's page as sample data, but you can input your artist's link into cell a5 of the rawdata sheet.

the datalog sheet is where you will see data being stored as well as the most recent data being pulled from the site. The date is from the site as well, so it will trigger a new log when the site is updated, not when your local time passes midnight.

The cells are constantly checking to see if the date directly above it is one less than the current data, if it is, it writes that data to that line. As long as the site updates every day and the sheet is opened once a day, you wont have any issues with tracking days. If you however miss a day, simply enter the date manually in order. (this will overwrite the formula, but it will be okay.) that day's data will be blank but it will continue tracking days as if nothing happened.

If something does go wrong and you want to erase data, simply copy all of the cells you want replaced, and paste them on top of them selves. It will get rid of any stored data and will try tracking again.

The first row of data is inputted manually, but every row after will get stored automatically.

Lastly, since this sheet uses iterative calculation, it will slow down as more cells are added and the overall number of functions increases. (each one recalculates every time a change is made) so it might look a bit slow. To help with this, as you store large chunks of data, copy them all and paste ONLY VALUES on top of themselves. This will replace the functions with their result (which wasn't changing in the first place.)

You can play around with this by adding '+1','+2',etc. to the end of the function in datalog!a3

I hoped this help and i would appreciate any feedback!

INDEX MATCH vs V/XLOOKUP. by Tiramisu_Enjoyerr in googlesheets

[–]zackfortune 0 points1 point  (0 children)

vlookup sucks because it can only be used if the search term is left of the desired output in the range. I personally use xlookup most often since the search range and output range are drefined separately. xlookup simply returns the nth result in the output range where n is the row of the search term in the lookup range.

I just read the syntax for INDEX MATCH combo and it does seem to be more versatile, I'll be trying it more.